Categories:

Query syntax

GROUP BY ROLLUP

GROUP BY ROLLUP is an extension of the GROUP BY clause that produces sub-total rows (in addition to the grouped rows). Sub-total rows are rows that further aggregate whose values are derived by computing the same aggregate functions that were used to produce the grouped rows.

You can think of rollup as generating multiple result sets, each of which (after the first) is the aggregate of the previous result set. So, for example, if you own a chain of retail stores, you might want to see the profit for:

  • Each store.

  • Each city (large cities might have multiple stores).

  • Each state.

  • Everything (all stores in all states).

You could create separate reports to get that information, but it is more efficient to scan the data once.

If you are familiar with the concept of grouping sets (GROUP BY GROUPING SETS) you can think of a ROLLUP grouping as equivalent to a series of grouping sets, and which is essentially a shorter specification. The N elements of a ROLLUP specification correspond to N+1 GROUPING SETS.

See also:

GROUP BY GROUPING SETS

Syntax

SELECT ...
FROM ...
[ ... ]
GROUP BY ROLLUP ( groupRollup [ , groupRollup [ , ... ] ] )
[ ... ]
Copy

Where:

groupRollup ::= { <column_alias> | <position> | <expr> }
Copy

Parameters

column_alias

Column alias appearing in the query block’s SELECT list.

position

Position of an expression in the SELECT list.

expr

Any expression on tables in the current scope.

Usage notes

  • As the query is aggregated at higher and higher levels, it shows NULL values in more columns of each row. This is appropriate. For example, in the example below, for the aggregate at the state level, the city column is NULL; that’s because the value in the profit column does not correspond to one city. Similarly, in the final total, which aggregates data from all the states and all the cities, the revenue is not from one specific state or one specific city, so both the state and city columns in that row are NULL.

  • The query should list the “most significant level” first in the parentheses after the ROLLUP. For example, states contain cities, so if you are rolling up data across states and cities, the clause should be

    ...ROLLUP (state, city)

    If you reverse the order of the column names, you get a result that is probably not what you want. In the example below, if you reversed the order of city and state in the ROLLUP clause, the result would be incorrect, at least in part because both California and Puerto Rico have a city named San Jose (“SJ”), and you probably would not want to combine the revenue from the two different San Joses, except in the final total of all revenue. (An alternative way to avoid combining data from different cities with the same name is to create a unique ID for each city and use the ID rather than the name in the query.)

Examples

Start by creating and loading a table with information about sales from a chain store that has branches in different cities and states/territories.

-- Create some tables and insert some rows.
CREATE TABLE products (product_ID INTEGER, wholesale_price REAL);
INSERT INTO products (product_ID, wholesale_price) VALUES 
    (1, 1.00),
    (2, 2.00);

CREATE TABLE sales (product_ID INTEGER, retail_price REAL, 
    quantity INTEGER, city VARCHAR, state VARCHAR);
INSERT INTO sales (product_id, retail_price, quantity, city, state) VALUES 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');
Copy

Run a rollup query that shows profit by city, state, and total across all states.

The example below shows a query that has three “levels”:

  • Each city.

  • Each state.

  • All revenue combined.

This example uses ORDER BY state, city NULLS LAST to ensure that each state’s rollup comes immediately after all of the cities in that state, and that the final rollup appears at the end of the output.

SELECT state, city, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit 
 FROM products AS p, sales AS s
 WHERE s.product_ID = p.product_ID
 GROUP BY ROLLUP (state, city)
 ORDER BY state, city NULLS LAST
 ;
+-------+---------+--------+
| STATE | CITY    | PROFIT |
|-------+---------+--------|
| CA    | SF      |     13 |
| CA    | SJ      |     26 |
| CA    | NULL    |     39 |
| FL    | Miami   |     48 |
| FL    | Orlando |     96 |
| FL    | NULL    |    144 |
| PR    | SJ      |    192 |
| PR    | NULL    |    192 |
| NULL  | NULL    |    375 |
+-------+---------+--------+
Copy

Some rollup rows contain NULL values. For example, the last row in the table contains a NULL value for the city and a NULL value for the state because the data is for all cities and states, not a specific city and state.

Language: English