Combining MySQL SUM and GROUP BY Clauses

This morning, I was working on putting together a stats page for my new Car Mileage Log when I ran into an intesting question: How does MySQL calculate the sum of a column that has been generated by a mathematical operation? Does it first sum both columns and then perform the math at the end? Or does it do the operation row by row and then sum up the results at the end? Through the experiment shown below, I found the latter to be true.

First, I set up a scenario where I knew that the operation was happening at the end and got $3,391.38 as the total price.

$result = mysql_query("SELECT SUM(`ppg`) AS `total_ppg`, COUNT(`car_id`) AS `qty`, SUM(`gallons`) AS `total_gallons` FROM `mileage` WHERE `car_id` = '$car_id' GROUP BY `car_id`");
$row = mysql_fetch_array($result);
echo "<p>Total Price: \$".number_format(($row['total_ppg']/$row['qty'])*$row['total_gallons'],2)."</p>" ;

Then, I set up a scenario where I knew that the operation was happening on each row and got $3,366.63 as my $total_price.

$total_price = 0;
$result = mysql_query("SELECT (`ppg` * `gallons`) AS `price` FROM `mileage` WHERE `car_id` = '$car_id'");
while ($row = mysql_fetch_array($result)) { $total_price = $total_price + $row['price']; }
echo "<p>Total Price: \$".number_format($total_price,2)."</p>" ;

Finally, to determine which would happen if I combined the two different clauses into one query, I did the following:

$result = mysql_query("SELECT SUM(`ppg` * `gallons`) AS `total_price` FROM `mileage` WHERE `car_id` = '$car_id' GROUP BY `car_id`");
$row = mysql_fetch_array($result);
echo "<p>Total Price: \$".number_format(($row['total_price'],2)."</p>" ;

The $row['total_price'] that I got in this last one was $3,366.63, which confirmed that MySQL was calculating the multiplication on each row and then combining those results at the end to generate the sum.


Comments

Loading…

This post was published on January 8th, 2011 by Robert James Reese in MySQL. Before using any of the code or other content in this post, you must read and agree to our terms of use.