Lesson 16.2

Subtotals and Grand Totals with ROLLUP in SQL

ROLLUP adds total rows to a grouped result, so a report carries its own subtotals and grand total.

A grouped report often needs a total row at the bottom. GROUP BY ROLLUP (…) adds it for you: alongside the normal per-group rows, it emits an extra row that aggregates across all groups.

In that total row, the grouped column is NULL because there is no single category it belongs to. Use coalesce() to give it a readable label, and grouping() to sort it to the bottom.

Pattern

select region, sum(amount) as total
from sales
group by rollup (region)

With more than one grouping column, ROLLUP (a, b) also produces per-a subtotals, not just the grand total. You get a full hierarchy of totals from one query.

Schema · Garden ShopTable · order_items5 columns · 48 rows

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id intorder_id intproduct_id intquantity intunit_price decimal

Your task

Report total revenue (quantity * unit_price) per category, with a grand-total row at the bottom labelled'All categories'. Return category andrevenue.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer