ROLLUP for hierarchical totals

ROLLUP automatically adds subtotal rows at each level of a grouping hierarchy, then caps everything with a grand total.

About 5–10 minutes · no sign-up needed

Example

Building Retail Hierarchical Totals with ROLLUP

SELECT category, brand, SUM(revenue) AS total_revenue
FROM store_sales
GROUP BY ROLLUP(category, brand)

Result:

Run the query without SUM so you can see what the unfiltered values look like.

SELECT category, brand, revenue
FROM store_sales
GROUP BY ROLLUP(category, brand);

SUM totals the numbers in a column. Works on numeric columns only.

SELECT category, brand, SUM(revenue)
FROM store_sales
GROUP BY ROLLUP(category, brand);

AS gives the SUM result a friendly column name so the output reads cleanly.

SELECT category, brand, SUM(revenue) AS total_revenue
FROM store_sales
GROUP BY ROLLUP(category, brand);

Try each one in your head first, then open the answer.

1. Using orders, write a query that returns hierarchical totals by order_id and customer_id using ROLLUP.

SELECT order_id, customer_id, COUNT(*) FROM orders GROUP BY ROLLUP(order_id, customer_id)

2. Using store_sales, write a query that returns hierarchical totals by sale_id and store_id using ROLLUP.

SELECT sale_id, store_id, COUNT(*) FROM store_sales GROUP BY ROLLUP(sale_id, store_id)

3. Using products, write a query that returns hierarchical totals by product_id and brand using ROLLUP.

SELECT product_id, brand, COUNT(*) FROM products GROUP BY ROLLUP(product_id, brand)

4. Using reviews, write a query that returns hierarchical totals by review_id and product_id using ROLLUP.

SELECT review_id, product_id, COUNT(*) FROM reviews GROUP BY ROLLUP(review_id, product_id)

5. Using employees, write a query that returns hierarchical totals by employee_id and full_name using ROLLUP.

SELECT employee_id, full_name, COUNT(*) FROM employees GROUP BY ROLLUP(employee_id, full_name)

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

About 5–10 minutes · no sign-up needed