Oct 10, 2018

[HDGEM] MySQL ROLLUP: Adding a WITH ROLLUP modifier to the GROUP BY clause causes the query to produce another row that shows the grand total

To summarize table contents per year, use a simple GROUP BY like this:
mysql> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year ASC; +------+--------+ | year | profit | +------+--------+ | 2000 | 4525 | | 2001 | 3010 | +------+--------+
The output shows the total profit for each year. To also determine the total profit summed over all years, you must add up the individual values yourself or run an additional query. Or you can use ROLLUP, which provides both levels of analysis with a single query. Adding a WITH ROLLUP modifier to the GROUP BY clause causes the query to produce another row that shows the grand total over all year values:
mysql> SELECT year, SUM(profit) AS profit         FROM sales         GROUP BY year ASC WITH ROLLUP;  +------+--------+  | year | profit |  +------+--------+  | 2000 |   4525 |  | 2001 |   3010 |  | NULL |   7535 |  +------+--------+  
The NULL value in the year column identifies the grand total super-aggregate line.


--
Posted By Blogger to HDGEM at 3/07/2017 12:00:00 PM