SQL Academy · Lesson

Beyond a Single GROUP BY

Aggregate at multiple levels.

Beyond a Single GROUP BY is a free SQL Academy lesson on CoddyKit — lesson 1 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.

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);

What Are GROUPING SETS?

GROUPING SETS lets you define multiple independent grouping levels inside one GROUP BY clause. Each set in the list produces its own group of rows, just as if you had written separate queries and combined them with UNION ALL.

The syntax is: GROUP BY GROUPING SETS ( (col1, col2), (col1), () ). The empty set () represents the grand total across all rows.

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

Reading GROUPING SETS Output

When you run a GROUPING SETS query, rows from different grouping levels are stacked together. Columns that are not part of a particular set appear as NULL in that row.

For example, a row that belongs to the (region) set will have NULL in the category column, signalling that this total spans all categories for that region. The grand-total row (empty set) has NULL in both region and category.

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

Introducing ROLLUP

ROLLUP is a shortcut for a common hierarchy of grouping sets. Given columns (A, B), ROLLUP(A, B) automatically generates the sets: (A, B), (A), and ().

This is perfect for hierarchical data such as year to month to day, or region to category. The number of sets produced is always n + 1, where n is the number of columns listed.

-- ROLLUP(region, category) is equivalent to:
-- GROUPING SETS ( (region, category), (region), () )

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

ROLLUP with Three Levels

Adding a third column to ROLLUP extends the hierarchy by one more level. ROLLUP(A, B, C) generates four sets: (A, B, C), (A, B), (A), and ().

In the example below, year is the outermost level and category is the most granular. The query produces subtotals at every step of the hierarchy plus one grand total row.

SELECT
  year,
  region,
  category,
  SUM(amount) AS total
FROM (
  VALUES
    (2024, 'North', 'Electronics', 1200),
    (2024, 'North', 'Clothing',     800),
    (2024, 'South', 'Electronics',  950),
    (2025, 'North', 'Electronics', 1400),
    (2025, 'South', 'Clothing',     700)
) AS t(year, region, category, amount)
GROUP BY ROLLUP(year, region, category)
ORDER BY year NULLS LAST, region NULLS LAST, category NULLS LAST;

Introducing CUBE

CUBE is even more expansive than ROLLUP. Given n columns, it generates all 2^n possible combinations of grouping sets, including the grand total.

For CUBE(region, category), the sets produced are: (region, category), (region), (category), and () — four sets in total. This is useful when you want cross-sectional totals in every direction, not just a single hierarchy.

-- CUBE(region, category) produces:
-- GROUPING SETS ( (region,category), (region), (category), () )

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY CUBE(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

CUBE vs ROLLUP — When to Use Which

Choose based on whether your dimensions have a natural hierarchy:

Use ROLLUP when the columns are hierarchical (e.g., country to city to store). Subtotals only roll up along one path.
Use CUBE when the columns are independent dimensions (e.g., region and category) and you want every possible cross-section.
Use GROUPING SETS when you need full control and neither ROLLUP nor CUBE matches your exact requirements.

The GROUPING() Function

Because NULL can mean either a genuine missing value or a grouping placeholder, SQL provides the GROUPING() function. It returns 1 when the column is part of a super-aggregate (placeholder) row, and 0 when the column was actually grouped on.

This allows you to distinguish a real NULL region from a subtotal row that spans all regions.

SELECT
  region,
  category,
  SUM(amount)        AS total,
  GROUPING(region)   AS is_region_subtotal,
  GROUPING(category) AS is_category_subtotal
FROM sales
GROUP BY CUBE(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

Using GROUPING() to Label Rows

A common pattern is to combine GROUPING() with a CASE expression to produce human-readable labels instead of raw NULL values. This makes the output of a ROLLUP or CUBE query much easier to read in reports.

SELECT
  CASE GROUPING(region)
    WHEN 1 THEN 'Grand Total'
    ELSE region
  END AS region_label,
  CASE GROUPING(category)
    WHEN 1 THEN 'All Categories'
    ELSE category
  END AS category_label,
  SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category)
ORDER BY GROUPING(region), region, GROUPING(category), category;

Mixing Fixed Columns with ROLLUP

You can mix regular GROUP BY columns with ROLLUP or CUBE in the same clause. Columns listed outside the ROLLUP(...) are always included in every grouping set — they are never rolled up.

In the query below, year is a fixed grouping column, while region and category participate in the rollup. This means you get subtotals per year, not across all years.

SELECT
  2024           AS year,
  region,
  category,
  SUM(amount)    AS total
FROM sales
GROUP BY 2024, ROLLUP(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

Quick Check

Test your understanding of GROUPING SETS, ROLLUP, and CUBE.

Recap — Beyond a Single GROUP BY

In this lesson you learned three powerful SQL extensions that let you produce multiple aggregation levels in a single query:

GROUPING SETS — full manual control; list exactly which combinations you need.
ROLLUP — ideal for hierarchies; rolls up from the most detailed level to the grand total.
CUBE — generates every possible cross-section of the listed dimensions.

You also learned that GROUPING() distinguishes genuine NULL values from super-aggregate placeholder rows, and that CASE GROUPING(...) patterns produce clean, readable report output.

These tools are indispensable whenever you need multi-dimensional summaries — pivot-style reports, dashboards, and data-warehouse queries all rely on them heavily.

Frequently asked questions

Is the “Beyond a Single GROUP BY” lesson free?

Yes — the full text of “Beyond a Single GROUP BY” 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 “Beyond a Single GROUP BY”?

Aggregate at multiple levels. 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 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Beyond a Single GROUP BY” 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