SUM() in CC 4.0 RC 3.2

carlsore 05 Aug, 2013
How do you do column totals? For another report I need, I have to report out total sales based on one master table ([Sales]).

I want the query to just be something straightforward, something like:
select salesperson, sum(amount) 
from Sales 
group by salesperson 
order by salesperson

How do I translate this into CC?
GreyHead 06 Aug, 2013
Hi Carlsore,

There are a couple of ways to do this.

If you want totals for the displayed list (which may not be all of the records in the table) and you are using a Custom Listing then you can use a global variable to keep a running total.

In the header:
global $col_total;
$col_total = 0;

In the body:
global $col_total;
$col_total += $row['col_name'];

In the footer:
global $col_total;
echo "Total is {$col_total}";

Or, if you want the total from the table you can add a new MySQL query in the Header or footer something like this:
$db =& JFactory::getDBO();
$query = "
    SELECT `salesperson` SUM(`col_name`) AS total
        FROM `#__table_name`
        GROUP BY `salesperson` ;
$totals = $db->loadObjectList();
foreach ( $totals as $t ) {
  echo "<p>{$t->salesperson} sold $($t->total}</p>";

carlsore 06 Aug, 2013
Worked like a charm! Beer's on me!

Many thanks,
This topic is locked and no more replies can be posted.