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 grouping() to distinguish that generated null from a real null in the source data, then label and sort the total row deliberately.

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 Shop Tables · order_items, products, categories 3 tables

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Table · categories

Lookup table of product categories.

2 columns · 8 rows

category_id int category_name text

Your task

Report total revenue (quantity * unit_price) per category, in alphabetical order, with a grand-total row at the bottom labeled 'All categories'. Return category and revenue.