content_image/744992.jpg content_image/745000.jpg
## 12.20.2 GROUP BY Modifiers 

The GROUP BY  clause permits a WITH ROLLUP  modifier that causes summary output to include extra rows that represent higher-level (that is, super-aggregate) summary operations. ROLLUP  thus enables you to answer questions at multiple levels of analysis with a single query. For example, ROLLUP  can be used to provide support for OLAP (Online Analytical Processing) operations. 

Suppose that a sales  table has year , country , product , and profit  columns for recording sales profitability: 

CREATE TABLE sales ( year INT, country VARCHAR(20), product VARCHAR(32), profit INT ); 

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; 

<img src='content_image/744992.jpg'>

The output shows the total (aggregate) 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 (super-aggregate) row that shows the grand total over all year values: 

mysql> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP; 

<img src='content_image/745000.jpg'>

The NULL  value in the year  column identifies the grand total super-aggregate line. 

ROLLUP  has a more complex effect when there are multiple GROUP BY  columns. In this case, each time there is a change in value in any but the last grouping column, the query produces an extra super- aggregate summary row. 

For example, without ROLLUP , a summary of the sales  table based on year , country , and product might look like this, where the output indicates summary values only at the year/country/product level of analysis: 

mysql> SELECT year, country, product, SUM(profit) AS profit FROM sales GROUP BY year, country, product; +------+---------+------------+--------+ 

