SQL Academy · Lesson

ROLLUP for Subtotals

Running subtotals and grand totals.

What Is ROLLUP?

When you group data with GROUP BY, you get one row per group. But often you also want subtotals for each group and a grand total for all rows. That is exactly what ROLLUP does.

ROLLUP is an extension of GROUP BY that automatically adds extra summary rows to your result set, giving you running subtotals at each level of the grouping hierarchy and a final grand total at the end.

Setting Up Sample Data

Let us create a simple sales table to practise with. It stores the region, product category, and the sale amount for each transaction. Run the statements below to create and populate it.

CREATE TABLE sales (
  region   TEXT,
  category TEXT,
  amount   NUMERIC
);

INSERT INTO sales VALUES
  ('East',  'Electronics', 500),
  ('East',  'Electronics', 300),
  ('East',  'Clothing',    150),
  ('West',  'Electronics', 700),
  ('West',  'Clothing',    200),
  ('West',  'Clothing',    100);

All lessons in this course

← Back to SQL Academy