11.15.2 GROUP BY Modifiers

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

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

This output shows the total profit for each year, but if you
also want to 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:

The grand total super-aggregate line is identified by the value
NULL in the year column.

ROLLUP has a more complex effect when there
are multiple GROUP BY columns. In this case,
each time there is a “break” (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 on the
sales table based on year,
country, and product might
look like this:

For this query, adding ROLLUP causes the
output to include summary information at four levels of
analysis, not just one. Here is how to interpret the
ROLLUP output:

Following each set of product rows for a given year and
country, an extra summary row is produced showing the total
for all products. These rows have the
product column set to
NULL.

Following each set of rows for a given year, an extra
summary row is produced showing the total for all countries
and products. These rows have the country
and products columns set to
NULL.

Finally, following all other rows, an extra summary row is
produced showing the grand total for all years, countries,
and products. This row has the year,
country, and products
columns set to NULL.

Other Considerations When using ROLLUP

The following items list some behaviors specific to the MySQL
implementation of ROLLUP.

When you use ROLLUP, you cannot also use an
ORDER BY clause to sort the results. In other
words, ROLLUP and ORDER BY
are mutually exclusive. However, you still have some control
over sort order. GROUP BY in MySQL sorts
results, and you can use explicit ASC and
DESC keywords with columns named in the
GROUP BY list to specify sort order for
individual columns. (The higher-level summary rows added by
ROLLUP still appear after the rows from which
they are calculated, regardless of the sort order.)

LIMIT can be used to restrict the number of
rows returned to the client. LIMIT is applied
after ROLLUP, so the limit applies against
the extra rows added by ROLLUP. For example:

Using LIMIT with ROLLUP
may produce results that are more difficult to interpret,
because you have less context for understanding the
super-aggregate rows.

The NULL indicators in each super-aggregate
row are produced when the row is sent to the client. The server
looks at the columns named in the GROUP BY
clause following the leftmost one that has changed value. For
any column in the result set with a name that is a lexical match
to any of those names, its value is set to
NULL. (If you specify grouping columns by
column number, the server identifies which columns to set to
NULL by number.)

Because the NULL values in the
super-aggregate rows are placed into the result set at such a
late stage in query processing, you cannot test them as
NULL values within the query itself. For
example, you cannot add HAVING product IS
NULL to the query to eliminate from the output all but
the super-aggregate rows.

On the other hand, the NULL values do appear
as NULL on the client side and can be tested
as such using any MySQL client programming interface.

MySQL permits a column that does not appear in the
GROUP BY list to be named in the select list.
In this case, the server is free to choose any value from this
nonaggregated column in summary rows, and this includes the
extra rows added by WITH ROLLUP. For example,
in the following query, country is a
nonaggregated column that does not appear in the GROUP
BY list and values chosen for this column are
indeterminate:

NULL rows are inserted very late in the query process, but they can be used very well as described in the post above. Also they can be used with the HAVING clause.

mysql> SELECT IFNULL(url, 'ALL_URLS') AS url, IFNULL(year, 'ALL_YEARS') AS year, IFNULL(country, 'ALL_COUNTRIES') AS country, SUM(visit) FROM rollup_1 GROUP BY url, year, country WITH ROLLUP HAVING country is null;