cswork / libraryOpen practice workspace

Articles

SQL GROUPING SETS Interview Questions: Subtotals, NULLs, and Double Counting

Practice SQL GROUPING SETS with a worked subtotal query, GROUPING masks, real versus generated NULLs, duplicate rows, and reconciliation checks.

An interviewer hands you a sales report with regional rows and one grand total. You add its revenue column and get three times the real sales. The SQL engine did not lose control of arithmetic; the output contains the same source money at several grouping levels. That is the central GROUPING SETS trap.

This guide uses PostgreSQL 18 syntax and a four-row input you can check by hand. It covers the questions that expose real understanding: which grouping sets produce which rows, why a subtotal looks like a stored NULL, how GROUPING() marks row grain, and how to reconcile without double counting.

What does GROUPING SETS ask the engine to return?

PostgreSQL's table-expression documentation defines GROUPING SETS as separate groupings over one input, with aggregate rows from each grouping returned together. It is like requesting several GROUP BY levels in one result. The values are not extra transactions. Each output row describes a different grain.

For GROUPING SETS ((region, product), (region), ()), the first set yields one row per observed region/product pair, the second yields one per region, and the empty set () yields a grand total. PostgreSQL replaces dimensions absent from a set with NULL in that result row. That replacement is the source of the ambiguity we will resolve below. The empty set is a single all-input group, even when there are no input rows; the behavior of individual aggregates on that empty input still matters.

The following input is original interview practice data. Treat amount as a non-null transaction amount; one source row has an unknown product. The duplicate East/Pen rows are two transactions, not accidental duplicates.

region  product  amount
East    Pen       10
East    Pen       20
East    NULL       5
West    Pen        7

Here is the PostgreSQL query. GROUPING(region, product) is a mask that says which named grouping expressions are absent from the current grouping set. The PostgreSQL aggregate-function reference documents this function; it is not an aggregate over the transaction values.

WITH sales(region, product, amount) AS (
  VALUES ('East', 'Pen', 10),
         ('East', 'Pen', 20),
         ('East', NULL, 5),
         ('West', 'Pen', 7)
)
SELECT region, product,
       GROUPING(region, product) AS grouping_mask,
       SUM(amount) AS revenue
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), ())
ORDER BY grouping_mask, region NULLS LAST, product NULLS LAST;

Before looking at a result, predict the number of rows: three observed detail groups, two regions, one grand total. That gives six. A candidate who predicts four has confused input transactions with output groups; a candidate who predicts every possible product/region combination has invented groups the input did not contain.

regionproductmaskrevenueGrain
EastPen030Detail
EastNULL05Detail with a stored unknown product
WestPen07Detail
EastNULL135Region subtotal; product omitted
WestNULL17Region subtotal; product omitted
NULLNULL342Grand total; both dimensions omitted

The output was cross-checked in a local SQL engine, while PostgreSQL documentation remains the source for PostgreSQL semantics. The result makes the key distinction visible: East / NULL appears twice, with different revenue and different masks. Only one is a row for transactions whose product is actually unknown.

Why GROUPING() is safer than COALESCE()

COALESCE(product, 'All products') changes both East rows with a null product into the same label. It cannot tell whether the null came from source data or from the grouping set's omitted dimension. The correct labeling decision first checks the grouping flag. For one column, GROUPING(product) is 1 when that column is omitted from the current set and 0 when it participates, even if its participating value is actually null.

CASE
  WHEN GROUPING(product) = 1 THEN 'ALL PRODUCTS'
  WHEN product IS NULL THEN '(unknown product)'
  ELSE product
END AS product_label

With two arguments, the rightmost expression supplies the low bit in PostgreSQL's mask. In this query, detail is binary 00 (decimal 0), regional subtotal is 01 (decimal 1), and grand total is 11 (decimal 3). A mask of 10 (decimal 2) would represent a product-only grouping in a suitable grouping-set list; it does not appear here. Rather than memorize arbitrary codes, derive each bit from whether its expression is in the set.

If region itself can be null in the source, a detail row for unknown region and known product would still have mask 0. A product-only subtotal would display a null region with mask 2. A grand total would display null in both dimensions with mask 3. The visible cell value never tells you by itself whether the dimension participated. Keep the mask as data until every consumer has assigned a level. A presentation-only label can be added later, but throwing the mask away at the SQL boundary recreates the ambiguity for downstream users.

This matters for filters as well as labels. WHERE product IS NULL filters input rows before grouping: it keeps the original unknown-product transaction and discards Pen transactions. HAVING GROUPING(product) = 1 filters result groups after grouping: it keeps subtotal and grand-total levels in this query. The PostgreSQL SELECT reference describes the grouping and HAVING stage. These two predicates are not substitutes, even though both can lead to visibly null product cells.

Where does double counting enter?

The raw four amounts sum to 42. The three detail rows also sum to 42; the two regional subtotals sum to 42; the grand-total row is 42. Adding all six output revenues gives 126, three times the source total, because the same money is represented once at each requested level. Each level is internally consistent. The combined result is not a bag of disjoint transactions.

Suppose a downstream dashboard groups this result by region and computes SUM(revenue). For East, it adds 30 + 5 + 35 and reports 70 instead of 35. The problem is not SUM itself; the downstream calculation mixed detail and subtotal grains. Export grouping_mask or an explicit level column with the result and filter to the level the chart intends to show. If the dashboard wants regional totals, use mask 1. If it wants an all-region total, use mask 3. If it wants product detail, use mask 0.

Percent-of-total calculations have the same risk. The East subtotal is 35/42, or about 83.3% of all revenue. If a window denominator sums the entire six-row result, it uses 126 and reports about 27.8% instead. Choose the grand-total row as the denominator, or compute the denominator separately from the filtered base transactions. Check that the region percentages sum to 100% under the intended population; if they sum to a third, you almost certainly mixed levels.

An interview answer should reconcile each grain independently. SUM(revenue) FILTER (WHERE grouping_mask = 0) should equal 42, as should the mask-1 equivalent, and the mask-3 value should be 42. This test assumes the requested detail groups partition the input and amount is non-null; in a real query, state the handling of null amounts, filters, currency, refunds and joins. Never treat the overall sum of subtotal and detail rows as a financial metric.

Joins can create a second, earlier multiplication. If each transaction joins to several product tags, SUM(amount) is inflated before GROUPING SETS runs. The grouping masks correctly label the levels of a wrong input. Check the row grain after joins, pre-aggregate one-to-many children, or join to one chosen dimension row. A mask is a defense against mixed output levels, not against upstream fan-out.

How do ROLLUP, CUBE, and duplicate sets differ?

ROLLUP(region, product) is shorthand for the exact three sets in the worked query: (region, product), (region), and (). Its order matters because it takes prefixes. ROLLUP(product, region) would instead create product subtotals. CUBE(region, product) adds all combinations, including the product-only set; that adds another level and another opportunity for a downstream total to count the same money twice.

Use explicit GROUPING SETS when the business request needs only selected levels. If it asks for detail, regional and product subtotals, but no grand total, list exactly those three sets. An unnecessary CUBE makes the result larger and its semantics harder to audit. Think of ROLLUP as a hierarchy, CUBE as all dimension combinations, and GROUPING SETS as the deliberate list.

PostgreSQL also documents that combined grouping items can generate the same grouping set more than once. Those duplicate grouping sets can emit duplicate aggregate rows. GROUP BY DISTINCT removes duplicate grouping sets before processing; SELECT DISTINCT removes identical output rows later, which is not the same operation and can hide legitimate equal-valued results. In an interview, first write out the expanded sets, then decide whether repetition was intended. Do not apply DISTINCT as a blind cure for a wrong join or mixed grain.

Interview debugging drill: find the exact wrong line

Imagine a report renders “East / All products / 40” and “Overall / All products / 42.” The source data above says East should be 35. Work backward in this order. First ask whether the source filter was applied before grouping and whether the raw East sum is 35. Second inspect join cardinality; an extra joined child may have added 5. Third check the output mask on every row. Fourth inspect downstream aggregation: did it sum detail plus subtotal or join subtotal rows back to detail? Fifth check labeling: did COALESCE turn a true unknown product into “All products”?

The point of the drill is to distinguish failures with evidence. A wrong label can coexist with correct revenue; a fan-out can make every grouping level wrong; a mixed-grain dashboard can make only the post-query display wrong. A strong candidate states the expected six rows, shows the mask, and compares each level to the four source transactions. “Add DISTINCT” is not a diagnosis.

For an empty input, the grand-total grouping set still yields an aggregate row, but SUM(amount) is null while COUNT(*) is zero in PostgreSQL. That can surprise a report that expects a numeric zero. Apply COALESCE(SUM(amount), 0) only if the business definition calls for zero, and keep the empty-period state observable if it might indicate a broken ingestion job. A missing data feed and a genuine no-sales day are operationally different.

Sources and further reading