SQL Aggregate Function as Table using PHP

SQL aggregates as defined by netizens is "perform a calculation on a set of values to return a single scalar value". We often use aggregate functions with GROUP BY, HAVING clause of SELECT statements.

While it is very simple showing tables with just one aggregate or a set of aggregate functions using SELECT and GROUP BY then showing the table as retrieved by the SELECT statement, the table is practically vertical listing in nature. For example

SELECT states, people, SUM(revenue) FROM sales GROUP BY states,people order by states DESC

will show when you format it as table from the query result directly.

People   Revenue   State
Sam       100,000    Selangor
Ken        200,000    Selangor
Joe         100,000    Johor

What if you want to show all the people in the state regardless whether there are sales made by individual and the percentage of their Revenue for the state? It is quite impossible to do it in a single select statement.

The following method shows how to use a single statement to get a list of people and their revenue plus percentage of the group.

First we create an associative array from a list of people and populate it in the array.

$keys=array('Sam','Ken','Joe','April');
$values=array('Revenue'=>0,'Percent'=>0);
$total=0; //totals of the group
$revenue=array_fill_keys($keys,$values)

Retrieve the data using the previous SELECT statement like below. The example uses SQLite.

$db=new SQLite3('revenue.db');
$result=$db->query("select people, sum(revenue) from sales group by people");
while ($row=$result->fetchArray(SQLITE3_NUM))){
$ppl=$row[0];
$revenue[$ppl]['Revenue']=$row[1];
$total +=$row[1];
}
$db->close();
//do calculation and populate table
foreach ($revenue as $key=>$row){
$percent=round($row['Revenue']/$total*100,2);
echo " {$key}
{$row['Revenue']}{$percent}";}

Obviously, the above could be done by 2 queries. One to retrieve the overall total and the other one as above. However, what if you want to list the sales by date and show the people in columns? That is not an easy task representing it in one or two SQL.

You could retrieve the query by revenue grouped by date and people. Your array setting will be

$keys=array('Date','Sam','Ken','Joe','April');
$people=array_fill_keys($keys,$values)

$revenue=Array(); //you could fill the array with every date in the period you choose.

In the while loop, do as follows (assuming date is unix datetime),

$rdate=$row[0]; // the date field

if (!isset($revenue['A'.$rdate])){

$revenue['A'.$rdate] =$people;

$revenue['A'.$rdate]['Date']=date('m/d/Y',$$rdate);

) // set up the array and populate the 'Date' of the array for easy retrieval later

$people = $row[1] //the people field

$revenue['A'.$rdate][$people]=$row[2]; // the revenue field

With this you could populate the whole columns of people with date as first column. Displaying the table is then a breeze using the FOREACH example above.