MySQL WITH ROLLUP modifier

Yesterday, I was solving simple database Query, where I needed to get some specific data. And while browsing thru MySQL documentation I found a very nice GROUP BY modifier which allows me make less queries and save some CPU on server. Let me explain how it works…

I have a table with invoices, where is bunch of data related to invoices. I needed to get a list of all invoices grouped by year and I also needed SUM of all invoices amounts from all of the years… Before I was solving this by two queries:

SELECT year, SUM(total_amount) FROM invoices GROUP BY year;
+------+-------------------+
| year | SUM(total_amount) |
+------+-------------------+
| 2000 |              4525 |
| 2001 |              3010 |
+------+-------------------+

and

SELECT SUM(total_amount) FROM invoices;
+--------------------+
| SUM(total_amount)  |
+--------------------+
|               7535 |
+--------------------+

This way of collecting data is being called two level analysis. As I said, this is being possible done using only one query with included GROUP BY modifier WITH ROLLUP:

SELECT IFNULL(year,"Total") as year, SUM(total_amount) FROM invoices GROUP BY year WITH ROLLUP;
+-------+--------------------+
| year  |  SUM(total_amount) |
+-------+--------------------+
| 2000  |               4525 |
| 2001  |               3010 |
| Total |               7535 |
+-------+--------------------+

I used a little improvement in query: IFNULL(year,”Total”) as year, because the row with rolledup data returns in column year NULL by default.
I hope the this modifier will make your life more easier now, as it did to me 🙂 Enjoy.