SQL Academy · Lesson

GROUPING SETS Explained

Pick exactly the groupings you want.

GROUPING SETS Explained is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Are GROUPING SETS?

When you write a GROUP BY clause, you define one set of columns to group by. But sometimes you need multiple different groupings in a single query — without running the query several times and using UNION ALL.

GROUPING SETS lets you specify exactly which groupings you want, all in one pass over the data. Each set in the list produces its own aggregated rows in the result.

Sample Table: sales

We will use a sales table throughout this lesson. It records transactions by region, product category, and amount.

Run the code to create and populate the table so you can follow along with every example.

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

INSERT INTO sales VALUES
  ('East',  'Electronics', 500),
  ('East',  'Clothing',    200),
  ('West',  'Electronics', 300),
  ('West',  'Clothing',    400),
  ('North', 'Electronics', 150),
  ('North', 'Clothing',    250);

The Old Way: UNION ALL

Before GROUPING SETS existed, getting totals at multiple levels meant writing separate queries and stacking them with UNION ALL. This is repetitive, harder to read, and scans the table multiple times.

The example below returns totals by region AND totals by category — using three separate SELECT statements.

SELECT region, NULL AS category, SUM(amount) AS total
FROM sales
GROUP BY region

UNION ALL

SELECT NULL, category, SUM(amount)
FROM sales
GROUP BY category

UNION ALL

SELECT NULL, NULL, SUM(amount)
FROM sales;

GROUPING SETS Syntax

The GROUPING SETS clause is placed inside (or instead of) a regular GROUP BY. You list each desired grouping as a parenthesised column list. An empty set () means the grand total — no grouping at all.

This single query does exactly what the three-part UNION ALL above did, but more concisely and in one table scan.

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (category),
  ()
);

Reading the Result

Each row in the output belongs to exactly one grouping set. When a column is not part of the active grouping set, its value is NULL.

A row with region = 'East' and category = NULL belongs to the (region) set.
A row with region = NULL and category = 'Electronics' belongs to the (category) set.
A row where both are NULL is the grand total from the () set.

Those NULLs are structural — they mean 'all values' for that dimension, not missing data.

Multi-Column Grouping Sets

Each grouping set can contain more than one column. The set (region, category) groups by both columns together, just like a plain GROUP BY region, category.

Combining that with individual sets and the grand total gives a four-level summary in a single query.

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  (category),
  ()
);

The GROUPING() Function

Because NULL can mean either 'this column was not grouped' or genuine missing data, SQL provides the GROUPING() function to tell them apart.

GROUPING(col) returns 1 when the column was omitted from the current grouping set (i.e., its NULL is structural), and 0 when the column is part of the grouping (or has a real NULL value).

SELECT
  region,
  category,
  SUM(amount)      AS total,
  GROUPING(region) AS is_region_total,
  GROUPING(category) AS is_category_total
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (category),
  ()
);

Replacing NULL Labels

A common pattern is to replace structural NULLs with a descriptive label so reports are easier to read. Use CASE WHEN GROUPING(...) = 1 THEN 'All ...' ELSE col END to substitute the label only for rollup rows.

SELECT
  CASE WHEN GROUPING(region)   = 1 THEN 'All Regions'    ELSE region   END AS region,
  CASE WHEN GROUPING(category) = 1 THEN 'All Categories' ELSE category END AS category,
  SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (category),
  ()
);

GROUPING SETS vs ROLLUP

ROLLUP(a, b) is a shorthand that expands to the grouping sets (a, b), (a), () — it always adds subtotals along a hierarchy and the grand total.

GROUPING SETS gives you full control: you choose exactly which combinations appear. If you don't need the full hierarchy, you can omit levels. The two queries below produce the same output.

-- Using ROLLUP
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category);

-- Equivalent explicit GROUPING SETS
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  ()
);

GROUPING SETS vs CUBE

CUBE(a, b) generates every possible combination of the listed columns: (a, b), (a), (b), (). It is useful for cross-dimensional analysis but produces many rows.

GROUPING SETS lets you pick only the combinations you care about, keeping the result focused and the query fast.

-- CUBE produces 4 grouping sets for 2 columns
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY CUBE(region, category);

-- GROUPING SETS: omit the (category)-only set if not needed
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  ()
);

Practical Use Case: Sales Dashboard

A real dashboard often needs row-level detail, department subtotals, and a grand total all in one result set. GROUPING SETS makes this trivial without any application-side aggregation or multiple round trips.

This query returns per-region-and-category detail rows, region-only subtotals, and a single grand total row — all sorted so the grand total appears last.

SELECT
  COALESCE(region,   'TOTAL')      AS region,
  COALESCE(category, 'ALL')        AS category,
  SUM(amount)                      AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  ()
)
ORDER BY
  GROUPING(region),
  region NULLS LAST,
  GROUPING(category),
  category NULLS LAST;

Quick Check

Test your understanding of GROUPING SETS.

Lesson Recap

Here is what you learned about GROUPING SETS:

GROUPING SETS lets you define multiple grouping combinations in a single query, replacing repetitive UNION ALL patterns.
Each set is listed as a parenthesised column list inside GROUP BY GROUPING SETS (...). An empty set () produces the grand total.
Columns not in the active set appear as NULL in that row — use GROUPING(col) to detect these structural NULLs.
ROLLUP and CUBE are convenient shorthands for common patterns, but GROUPING SETS gives you full, precise control over which groupings are included.
Combine with COALESCE or CASE WHEN GROUPING(...) to replace NULLs with readable labels in reports.

Frequently asked questions

Is the “GROUPING SETS Explained” lesson free?

Yes — the full text of “GROUPING SETS Explained” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “GROUPING SETS Explained”?

Pick exactly the groupings you want. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “GROUPING SETS Explained” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy