SQL Academy · Lesson

Beyond a Single GROUP BY

Aggregate at multiple levels.

The Limitation of a Single GROUP BY

A standard GROUP BY clause lets you aggregate rows at one specific level — for example, total sales per region. But what if you also want totals per product category, and a grand total, all in the same query?

Repeating the query three times and using UNION ALL works, but it is verbose and slow. SQL provides three powerful extensions — GROUPING SETS, ROLLUP, and CUBE — that solve this problem elegantly in a single pass.

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Setting Up the Example Table

Throughout this lesson we will use a simple sales table that records each sale with a region, a category, and an amount. Let us create and populate it so every upcoming query makes sense in context.

CREATE TABLE sales (
  id       SERIAL PRIMARY KEY,
  region   TEXT,
  category TEXT,
  amount   NUMERIC
);

INSERT INTO sales (region, category, amount) VALUES
  ('North', 'Electronics', 1200),
  ('North', 'Clothing',     800),
  ('South', 'Electronics',  950),
  ('South', 'Clothing',     600),
  ('East',  'Electronics', 1100),
  ('East',  'Clothing',     750);

All lessons in this course

← Back to SQL Academy