Forums

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?

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:

<?php
global $col_total;
$col_total = 0;
?>

In the body:

<?php
global $col_total;
$col_total += $row['col_name'];
?>

In the footer:

<?php
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` ;
";
$db->setQuery($query);
$totals = $db->loadObjectList();
foreach ( $totals as $t ) {
  echo "<p>{$t->salesperson} sold $($t->total}</p>";
}
?>

Bob

Worked like a charm! Beer's on me!

Many thanks,
Robb