SQL Query Fundamentals Questions

GROUPING() (or the combined GROUPING_ID() in engines that support it) returns 1 for a grouping column that's been rolled up to NULL as part of a subtotal, and 0 when that column holds a real, detail-level value, letting you reliably tell a genuine NULL apart from a ROLLUP-generated one.

Structured elaboration

SELECT category, subcategory, SUM(amount) AS total_amount,
  CASE
    WHEN GROUPING(category) = 1 THEN 'grand_total'
    WHEN GROUPING(subcategory) = 1 THEN 'category_total'
    ELSE 'detail'
  END AS level_label
FROM sales
GROUP BY ROLLUP(category, subcategory);

Without GROUPING(), a NULL in the subcategory column of the result is ambiguous: it might be a genuine data value (if subcategory itself can legitimately be NULL) or it might be ROLLUP's signal for "this row sums across all subcategories". GROUPING(subcategory) resolves the ambiguity directly: 1 always means "rolled up here", regardless of whether the underlying data ever has a genuine NULL subcategory.

Worked example

Given sales(category, subcategory, amount) with rows (a,x,10), (a,y,20), (b,x,5): the detail level shows category='a', subcategory='x', total=10, level='detail' (and similarly for the other two detail combinations); the category-subtotal level shows category='a', subcategory=NULL, total=30 (10+20 combined), level='category_total', and a second subtotal row for category='b', subcategory=NULL, total=5 (the single b/x row rolled up on its own, since it has no other subcategory to combine with); the grand-total level shows category=NULL, subcategory=NULL, total=35 (30 from category 'a' plus 5 from category 'b'), level='grand_total'. In total, ROLLUP(category, subcategory) over these 3 detail rows produces 6 output rows: 3 detail + 2 category subtotals (one per distinct category) + 1 grand total.

Trade-offs and pitfalls

GROUPING_ID() (a single combined value encoding which columns were rolled up, as a bitmask) is more convenient than checking each GROUPING() call separately once there are more than two or three grouping columns, though not every engine supports it identically; check your specific engine's documentation before assuming syntax parity with another.

GROUP BY 1, 2 (positional) is terser but fragile under refactoring; repeating the full expression is explicit but verbose and error-prone to keep in sync; a CTE (Common Table Expression) with a named alias is usually the most maintainable middle ground.

Structured elaboration

-- Positional: fragile if SELECT list columns get reordered
SELECT region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2;
-- Repeated expression: verbose, and easy to accidentally diverge from the SELECT list
SELECT region, DATE_TRUNC('month', order_date) AS month, SUM(amount)
FROM orders GROUP BY region, DATE_TRUNC('month', order_date);
-- CTE with alias: compute once, group by the readable name
WITH base AS (
  SELECT region, DATE_TRUNC('month', order_date) AS month, amount FROM orders
)
SELECT region, month, SUM(amount) FROM base GROUP BY region, month;

Positional GROUP BY is standard SQL and works everywhere, but if someone later reorders or adds a column to the SELECT list, the grouping silently changes to match different columns, a genuinely dangerous refactoring hazard. Repeating the expression avoids that but means the transformation logic is duplicated and can drift if only one copy is edited. A CTE materializes the computed expression once under a name, so both the SELECT list and the GROUP BY reference the same alias with no duplication.

Worked example

Given orders(region, order_date, amount) with rows (US, 2024-01-05, 100), (US, 2024-01-20, 50), (EU, 2024-01-10, 30), (US, 2024-02-01, 40): the original query, SELECT region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2, correctly returns three rows: EU/January 30, US/January 150, US/February 40.

Now suppose a teammate inserts customer_segment as a new first column in the SELECT list (every row here happens to be segment 'B2C') and forgets to update the GROUP BY: SELECT customer_segment, region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2. The GROUP BY clause still says 1, 2, but those ordinals now point at customer_segment and region, not region and the month expression. Because the third column (the month) is no longer part of the grouping, engines diverge sharply on what happens: strict engines like PostgreSQL raise an error (every non-aggregated SELECT column must appear in GROUP BY), while others (SQLite, and MySQL outside strict mode) silently pick an arbitrary month value per group and merge everything else. On the latter, the result silently collapses to two rows, B2C/EU 30 and B2C/US 190, with the January 150 and February 40 for US silently summed together into one bucket, no error and no warning that two different months got merged.

Trade-offs and pitfalls

For a one-off, throwaway query, positional GROUP BY is fine and common in ad hoc analysis. For anything that will live in a dashboard, a scheduled report, or a codebase, prefer the CTE-with-alias pattern: it reads clearly and is safe under later edits to the SELECT list.

Labeling the NULL group with COALESCE is a display-layer fix; it's worth thinking through separately from whether NULLs should also be preserved for analysis, and from what happens when that labeled value later needs to join back to a real dimension table.

Structured elaboration

SELECT COALESCE(customer_segment, 'Unknown') AS segment, COUNT(*) AS n
FROM customers
GROUP BY COALESCE(customer_segment, 'Unknown');

Two considerations beyond the basic COALESCE-for-display pattern:

1. Keeping the NULL group visible for analysis: sometimes you want both the labeled dashboard view AND a way to drill into "why do these customers have no segment at all" (a data-quality question in its own right), so don't let the COALESCE'd label become the only representation of that data downstream. Concretely, run a dedicated query alongside the COALESCE one: SELECT * FROM customers WHERE customer_segment IS NULL. Against the worked-example data below (customers 2 and 4), this returns exactly those two rows in full, letting an analyst drill into who they are and why their segment is missing, something the COALESCE'd dashboard view alone can't answer since it has already collapsed both rows into a single 'Unknown' count.
2. Joining back to a segment dimension table: if 'Unknown' is joined against a real segments dimension table that has no row for the literal string 'Unknown', the join will silently drop those rows (an INNER JOIN) or produce NULLs for the dimension's other columns (a LEFT JOIN), which is a second, distinct NULL-handling decision layered on top of the first.

Worked example

Given customers(customer_id, customer_segment) with rows (1,'SMB'), (2,NULL), (3,'Enterprise'), (4,NULL): the query correctly groups the two NULL customers under 'Unknown', alongside SMB (1) and Enterprise (1), for three total groups.

Trade-offs and pitfalls

The failure mode to watch for is compounding: COALESCE handles the grouping and display, but if a later join to a dimension table isn't also NULL-aware (or Unknown-aware), the same underlying "missing segment" data problem resurfaces as a second, harder-to-spot bug at the join step.

The error means the engine can't decide which value of product_name to display for a category that has more than one; SQL requires every non-grouped, non-aggregated column in the SELECT list to have a single, unambiguous value per group, and by default it refuses to guess.

Structured elaboration

SELECT category, product_name FROM sales GROUP BY category groups rows by category, but if a category has multiple distinct product_name values, the engine has no rule for picking one (some engines, like MySQL in non-strict mode, will silently pick an arbitrary one instead of erroring, which is arguably worse than an error). Two genuinely different fixes depending on intent:

-- Pick one representative product per category (e.g., alphabetically first)
SELECT category, MIN(product_name) AS a_product FROM sales GROUP BY category;
-- List every distinct category/product pair, no aggregation at all
SELECT DISTINCT category, product_name FROM sales;

Worked example

Given sales(category, product_name) with rows ('a','p1'), ('a','p2'), ('b','p3'): the representative-row fix returns ('a','p1'), ('b','p3') (p1 chosen via MIN as an arbitrary but deterministic tie-breaker). The list-all-pairs fix returns all three rows unchanged, since there's no aggregation happening at all.

Trade-offs and pitfalls

The two fixes answer genuinely different business questions, not two syntaxes for the same thing: "give me one product per category" versus "show me every product-category combination that exists". Picking the wrong one produces a query that runs without error but silently answers a question nobody asked.

A percentage-of-total calculation needs the total computed independently, either via a subquery or a window function, and a NULLIF guard against a zero total to avoid a division-by-zero error when there are no rows at all.

Structured elaboration

SELECT status, COUNT(*) AS n,
  ROUND(100.0 * COUNT(*) / NULLIF((SELECT COUNT(*) FROM orders), 0), 2) AS pct
FROM orders
GROUP BY status;

The subquery (SELECT COUNT(*) FROM orders) computes the grand total independently of the outer GROUP BY, giving each status row access to the same shared denominator. NULLIF(..., 0) turns a genuinely empty orders table's total (0) into NULL before it hits the division, avoiding a division-by-zero error (or, on engines that don't error, an undefined/infinite result) and instead cleanly returning NULL percentages when there's nothing to compute a percentage of.

Worked example

Given orders with statuses shipped, shipped, cancelled, pending (4 total): shipped gets count 2 and percentage 50.0%; cancelled and pending each get count 1 and percentage 25.0%. The three percentages correctly sum to 100%.

Trade-offs and pitfalls

A useful sanity check on any percentage-of-total query: confirm the computed percentages across all groups actually sum to 100 (accounting for rounding), since a bug in the denominator (e.g., accidentally scoping the subquery to a filtered subset instead of the true grand total) often produces percentages that sum to something other than 100 without any error being raised.