Advanced GROUP BY Techniques You Should Know

Beyond plain GROUP BY

A regular GROUP BY gives you exactly one row per group — useful, but limited. If you've read GROUP BY Mistakes That Break SQL Queries, you already know the basics cold. This is what comes next: getting subtotals, grand totals, and pivoted layouts in the same query, instead of stitching several queries together with UNION.

Here's the sales data we'll use throughout:

ROLLUP — subtotals and a grand total

ROLLUP(region, product) gives you the full detail, then rolls up: a subtotal per region, then one grand total. Think of it as progressively dropping columns from the right:

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

Seven rows from four rows of data: 4 detail rows, 2 region subtotals (product is NULL), and 1 grand total (both columns NULL).

CUBE — every combination

CUBE(region, product) goes further: it computes a subtotal for every combination, including a product-only subtotal that ROLLUP never produces in this column order:

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

Nine rows now — the same seven from ROLLUP, plus Widget: 300 and Gadget: 200, the product-only subtotals across both regions.

GROUPING SETS — full control

Sometimes you want neither the full ROLLUP hierarchy nor CUBE's everything-at-once. Say you want only the region subtotals and only the product subtotals — no full detail rows, no grand total. Neither ROLLUP nor CUBE can produce exactly that on their own; GROUPING SETS can, because you list the exact combinations yourself:

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

Exactly four rows, exactly what was asked for — no detail, no grand total. ROLLUP(region, product) and CUBE(region, product) are actually just shorthand: they expand into a specific, fixed list of grouping sets under the hood. GROUPING SETS is that same mechanism, fully exposed.

Telling a subtotal apart from a real NULL

If your data can legitimately contain a NULL region or product, a subtotal row becomes ambiguous — is this NULL "all regions," or a real unrecorded region? The GROUPING() function answers that directly: 1 means "this NULL is a generated subtotal," 0 means "this is a real grouped value":

SELECT region, product, SUM(amount) AS total,
       GROUPING(region)  AS is_region_subtotal,
       GROUPING(product) AS is_product_subtotal
FROM sales
GROUP BY ROLLUP(region, product);

On the grand total row, both flags are 1. On a region subtotal row, only is_product_subtotal is 1. On a genuine detail row, both are 0 — even if a real product name happened to be NULL in your source data.

Conditional aggregation — pivoting without PIVOT

Not every advanced GROUP BY need is about subtotals — sometimes you want row values turned into columns. Wrapping an aggregate around a CASE expression does this in any SQL database, no special PIVOT syntax required:

SELECT product,
       SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) AS north,
       SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) AS south
FROM sales
GROUP BY product;

Each region became its own column — a genuine pivot table, built entirely from GROUP BY and CASE.

Which one should you use?

• Need a natural hierarchy of subtotals down to a grand total? Use ROLLUP — regional reports, date hierarchies (year → quarter → month).
• Need every possible cross-tab combination for analysis? Use CUBE — but check the row count first.
• Need a specific, non-standard combination of subtotals? Use GROUPING SETS — full control, no unwanted rows.
• Need row values as columns, like a spreadsheet pivot? Use conditional aggregation with CASE.

Database support

Conditional aggregation with CASE, on the other hand, works identically everywhere — it's plain GROUP BY with no special syntax to support.

Common mistakes

• Confusing a subtotal's NULL with a real NULL value. Use GROUPING() instead of guessing — especially in a report that gets filtered downstream.
• Reaching for CUBE by default. With several grouping columns, CUBE's row count explodes combinatorially — ROLLUP or GROUPING SETS is usually what you actually need.
• Forgetting MySQL's syntax is different. MySQL uses GROUP BY region, product WITH ROLLUP, not GROUP BY ROLLUP(region, product) — and has no CUBE or GROUPING SETS at all.
• Writing several separate GROUP BY queries joined with UNION when a single GROUPING SETS query would scan the table once instead of several times.