Get sum of VARCHAR field values by date

Question

I have 2 tables with the following structures:

CREATE TABLE IF NOT EXISTS `campaigns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `campaign_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_campaign` int(11) NOT NULL,
  `day` int(11) NOT NULL,
  `cost` varchar(255) NOT NULL,
  `conv` int(11) NOT NULL,
  `cost_conv` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1457 ;

Now the cost and cost_conv fields are dollar values such as 12.13 or 143.00.

The day field contains a unix timestamp stored as an INT.

What I am trying to do is get the sum of the cost, conv, and cost_conv field's for yesterday and output them into an HTML table.

Here is my code:

<h2>Yesterday</h2>

<?php

$sql = mysql_query("
                    select c.name,
                    (select sum(conv) from campaign_stats where id_campaign=c.id and day=date(from_unixtime(day) - interval 1 day)) as num_conv,
                    (select sum(cast(cost as float)) from campaign_stats where id_campaign=c.id and day=date(from_unixtime(day) - interval 1 day)) as num_cost,
                    (select sum(cast(cost_conv as float)) from campaign_stats where id_campaign=c.id and day=date(from_unixtime(day) - interval 1 day)) as num_cost_conv
                    from campaigns as c
                    order by c.name
                    ") or die(mysql_error());

if (mysql_num_rows($sql) == 0)
{
    echo '<p>No campaign information to display.</p>';
}
else
{
    echo '<table cellpadding="10" cellspacing="1" border="0" width="100%">';
    echo '<tr>';
    echo '<th>Account</th>';
    echo '<th>Conversions</th>';
    echo '<th>Cost</th>';
    echo '<th>Cost per Conversion</th>';
    echo '</tr>';

    while ($row = mysql_fetch_assoc($sql))
    {
        foreach ($row as $k => $v)
            $$k = htmlspecialchars($v, ENT_QUOTES);

        echo '<tr class="'.(($count % 2) ? 'row1' : 'row2' ).'">';
        echo '<td>'.$name.'</td>';
        echo '<td>'.$num_conv.'</td>';
        echo '<td>'.$num_cost.'</td>';
        echo '<td>'.$num_cost_conv.'</td>';
        echo '</tr>';

        $count++;
    }

    echo '</table>';
}

?>

It is giving me the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'float)) from campaign_stats where id_campaign=c.id and day=date(from_unixtime(da' at line 2

Honestly I am not even sure if I wrote the query properly. Any help would be greatly appreciated.

Answer 1

First of all, I don't like how your query is written, you could replace subqueries with a single join. Your query could be something like this:

select
  campaigns.name,
  sum(campaign_stats.conv) as num_conv,
  sum(cast(campaign_stats.cost as float)) as num_cost,
  sum(cast(campaign_stats.cost_conv as float)) as num_cost_conv
from
  campaigns inner join campaign_stats on campaigns.id=campaign_stats.id_campaign
where
  date(campaign_stats.day) = date_sub(date(now()),interval 1 day)
group by
  campaigns.name
order by
  campaigns.name