What do ROLLUP and CUBE add over a plain GROUP BY, and which dialects support them?
They add rows to the answer that are not in the data. The rows are the totals, and that is the whole idea.
The feature ROLLUP gives you the total for each combination of the grouping columns, and it works down from the most detailed level to the least. So with a group by on the customer and the status, the rollup gives you the total for each customer and status pair, then the total for each customer, and then the grand total.
The feature CUBE gives you the total for every combination, including the ones where some of the grouping columns are left out. So the cube gives you the pairs, the customer totals, the status totals, and the grand total.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
-- The rollup. Three levels of detail.
SELECT customer_id, status, SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (customer_id, status);
So which dialects support them? Here is the list.
PostgreSQL has both of them, and they are written exactly like that. Oracle has both. SQL Server has both, but it writes them the other way round, with the keyword after the group by, so you write GROUP BY customer_id, status WITH ROLLUP. MySQL has the rollup, and it was added in MySQL 8, and it does not have the cube.
And SQLite has neither of them, which is worth knowing because SQLite is the engine that a lot of learning examples use.
There is one thing that you need whichever engine you are on, and that is the function that tells you which level a total belongs to. Without it you cannot tell a subtotal apart from a real row, because the value is missing in it.
-- PostgreSQL. The grouping function says whether a column
-- is missing because of the rollup.
SELECT
customer_id,
status,
GROUPING(customer_id) AS customer_is_total,
GROUPING(status) AS status_is_total,
SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (customer_id, status);
So the rule to remember is this. The rollup gives you the totals down the chain, the cube gives you the totals for every combination, and they are not in the standard. If you need the same answer in every engine, use a union instead.
The last question in this topic shows that portable version.