Subtotals

[cogdev]

I am using group by on a table. How can I include a subtotal for every new group, and a grand total at the end?

I have a mysql database.

 

[sleipnir214]

MySQL can do the per-category subtotals for you. The grand total you'll have to calculate in PHP.

Given the following data in a table foo:

[tt]+----------+------+
| category | val |
+----------+------+
| a | 1 |
| a | 2 |
| c | 3 |
| c | 8 |
| b | 6 |
| b | 5 |
| d | 4 |
+----------+------+[/tt]

The query:

select category, sum(val) as sum from foo GROUP by category order by category;

will return:

[tt]+----------+------+
| category | sum |
+----------+------+
| a | 3 |
| b | 11 |
| c | 11 |
| d | 4 |
+----------+------+[/tt]


You can then loop through the values, maintaining a variable which accumulates the sums.


Want the best answers? Ask the best questions!

TANSTAAFL!!