Construct multi-level hierarchical subtotals with ROLLUP, master directional hierarchy order, and safely disambiguate subtotal NULLs with GROUPING().
Open in Present mode · 400 practice questions · Live class schedule · Try the playground
Jo class mein bola, yahan nukte mein likha hai.
What I said in class, written here as key points.
Construct multi-level hierarchical subtotals with ROLLUP, master directional hierarchy order, and safely disambiguate subtotal NULLs with GROUPING().
From flat categorical groups to executive hierarchical subtotals
Transitioning from flat category partitions to multi-level hierarchical subtotal pyramids
ROLLUP(A, B) generates 3 grouping sets: (A, B), (A), and (). Column order is directional: it strips columns right-to-left. GROUPING() returns 1 for aggregated subtotals and 0 for detailed rows.SELECT store_id, payment_mode_id, COUNT(*) AS orders, ROUND(SUM(net_total), 2) AS rev, CASE WHEN GROUPING(store_id) = 1 THEN 'Grand Total' WHEN GROUPING(payment_mode_id) = 1 THEN 'Store Subtotal' ELSE 'Store & Mode Detail' END AS summary_level FROM sales.orders WHERE store_id IN (1, 2) GROUP BY ROLLUP ( store_id, payment_mode_id ) ORDER BY store_id NULLS LAST, payment_mode_id NULLS LAST; -- Single scan evaluates all 3 levels
UNION ALL. Use native ROLLUP to evaluate N+1 hierarchical subtotals in a single memory-optimized pass with zero risk of logic drift.Connecting organizational reporting hierarchies to SQL grouping extensions.
GROUPING() function detects whether NULL is a generated subtotal or a true missing value.Click pyramid levels below to observe how ROLLUP progressively collapses columns from right to left.
ROLLUP(c1, c2, c3), PostgreSQL produces exactly N + 1 aggregation grouping sets. 3 columns produce 4 grouping sets.NULL to that column in the output row.Automating multi-level hierarchical subtotals with concise declarative syntax.
ROLLUP (year, quarter) is syntactic shorthand for:
GROUP BY GROUPING SETS ( (year, quarter), (year), () )
() represent the grand total across the entire filtered table, with both year and quarter set to NULL.ORDER BY ... NULLS LAST so grand totals appear at the bottom!SELECT EXTRACT(YEAR FROM order_date)::INT AS order_year, EXTRACT(QUARTER FROM order_date)::INT AS order_quarter, COUNT(*) AS total_orders, ROUND(SUM(net_total), 0) AS revenue FROM sales.orders GROUP BY ROLLUP ( EXTRACT(YEAR FROM order_date)::INT, EXTRACT(QUARTER FROM order_date)::INT ) ORDER BY order_year NULLS LAST, order_quarter NULLS LAST LIMIT 15;
Click order strategies below to understand why column sequence inside ROLLUP dictates output logic.
(A, B) and (B, A) are identical, ROLLUP(A, B) and ROLLUP(B, A) generate completely different intermediate subtotals!city, region) calculates city totals across regions, missing regional management subtotals entirely.Why column placement in ROLLUP determines the semantic meaning of intermediate subtotals.
-- Correct: Supplier > Brand ROLLUP (supplier_id, brand_id) -- Sets: (s, b), (s), ()
-- Inverted: Brand > Supplier ROLLUP (brand_id, supplier_id) -- Sets: (b, s), (b), ()
SELECT supplier_id, brand_id, COUNT(*) AS total_skus, ROUND(AVG(price), 0) AS avg_price FROM products.products WHERE supplier_id IN (1, 2) GROUP BY ROLLUP (supplier_id, brand_id) ORDER BY supplier_id NULLS LAST, brand_id NULLS LAST LIMIT 15;
Click grouping modes below to observe how composite column parentheses bind dimensions together.
(quarter, month) treats them as a single atomic unit that cannot be split during the rollup progression.work_mem buffers.Using composite column groupings to control which hierarchical levels PostgreSQL computes.
GROUP BY ROLLUP ( store_id, (year, quarter) )
1. (store_id, year, quarter) 2. (store_id) 3. ()Notice that
(store_id, year) without quarter is skipped!(year, quarter), (first_name, last_name), or (item_id, item_name) that always travel together.SELECT store_id, EXTRACT(YEAR FROM order_date)::INT AS order_year, EXTRACT(QUARTER FROM order_date)::INT AS order_quarter, COUNT(*) AS total_orders, ROUND(SUM(net_total), 0) AS revenue FROM sales.orders WHERE store_id IN (1, 2) GROUP BY ROLLUP ( store_id, (EXTRACT(YEAR FROM order_date)::INT, EXTRACT(QUARTER FROM order_date)::INT) ) ORDER BY store_id NULLS LAST, order_year NULLS LAST, order_quarter NULLS LAST LIMIT 15;
Click record types below to contrast genuine NULL database values against engine-generated subtotal NULLs.
NULL in standard SQL output. A human cannot tell if a row is an online order or a grand total!COALESCE(store_id, 'All Stores') blindly replaces real NULL stores with “All Stores”, corrupting management figures!GROUPING(col) function inspects the internal grouping state: returns 0 for real rows, and 1 for aggregated subtotals.Understanding why synthetic subtotal NULLs cause visual ambiguity and reporting errors.
NULL in the result to represent “all values of this dimension combined”.store_id, or guests with no customer_id).COALESCE(store_id::TEXT, 'Grand Total') matches BOTH! It labels real online orders as “Grand Total”, producing duplicate grand total rows!SELECT store_id, COUNT(*) AS total_orders, ROUND(SUM(net_total), 0) AS revenue FROM sales.orders WHERE store_id IS NULL OR store_id = 1 GROUP BY ROLLUP (store_id) ORDER BY store_id NULLS LAST LIMIT 10;
Click row states below to observe how GROUPING() returns 0 for grouped columns and 1 for aggregated subtotals.
GROUPING(col) = 0 guarantees that the column is actively part of the grouping set. The value is authentic data, not a subtotal.GROUPING(col) = 1 indicates that the column was collapsed into an aggregate subtotal. It is 100% safe to replace NULL with a presentation label.CASE WHEN GROUPING(col) = 1 THEN 'All' ELSE col::TEXT END for production C-suite reporting.Using ANSI SQL GROUPING() to construct polished, enterprise-ready dashboard labels.
GROUPING(column_name). It returns integer 0 or 1. Valid only in queries with ROLLUP, CUBE, or GROUPING SETS.CASE WHEN GROUPING(dept) = 1 THEN 'All Depts' ELSE dept END AS dept_name
GROUPING(a)=1 AND GROUPING(b)=1), you are at the apex grand total of the entire organization!SELECT
CASE
WHEN GROUPING(store_id) = 1 THEN 'All Stores'
ELSE 'Store ' || store_id::TEXT
END AS store_label,
CASE
WHEN GROUPING(order_status) = 1 THEN 'All Statuses'
ELSE order_status
END AS status_label,
COUNT(*) AS total_orders,
ROUND(SUM(net_total), 0) AS revenue
FROM sales.orders
WHERE store_id IN (1, 2)
GROUP BY ROLLUP (store_id, order_status)
ORDER BY store_id NULLS LAST, order_status NULLS LAST
LIMIT 15;
Six critical anti-patterns that corrupt subtotal calculations and dashboard presentations.
ROLLUP(city, region) instead of ROLLUP(region, city). This rolls up by city first, missing regional totals entirely!COALESCE(col, 'Total') without GROUPING(). If a customer or store is genuinely NULL, COALESCE falsely labels it “Total”.NULLS LAST in ORDER BY. In PostgreSQL ASC, NULL sorts FIRST, placing grand totals at the top instead of the bottom.GROUPING(col) in a standard GROUP BY query without ROLLUP, CUBE, or GROUPING SETS. PostgreSQL raises a syntax error.High-frequency technical questions asked at Amazon, Swiggy, and enterprise data engineering rounds.
ROLLUP automates hierarchical multi-level subtotals and grand totals in a single table pass. For N columns, it generates exactly N + 1 grouping sets, progressively stripping dimensions from right to left.ROLLUP(year, quarter, month) generates 4 sets: (year, quarter, month), (year, quarter), (year), and (). It reduces I/O by 60%–75% compared to chaining separate GROUP BY queries with UNION ALL.Click any card to flip and verify your understanding of ROLLUP subtotal mechanics.
Core architectural rules for production multi-level subtotal reporting.
ROLLUP(parent, child).(a, (b, c)) to bind them atomically and suppress intermediate subtotal noise.GROUPING(col) = 1 to reliably detect synthetic subtotals without masking genuine database NULLs.() collapses all partitions into a single company-wide grand total row matching full-table aggregate functions.NULLS LAST in ORDER BY so executive grand totals appear at the bottom of reports rather than the top.Production SQL syntax reference for hierarchical aggregation and presentation formatting.
SELECT EXTRACT(YEAR FROM order_date)::INT AS yr, COUNT(*) AS cnt FROM sales.orders GROUP BY ROLLUP (yr) ORDER BY yr NULLS LAST LIMIT 10;
SELECT store_id, EXTRACT(YEAR FROM order_date)::INT AS yr, COUNT(*) AS cnt FROM sales.orders WHERE store_id IN (1, 2) GROUP BY ROLLUP (store_id, (yr)) ORDER BY store_id NULLS LAST LIMIT 10;
SELECT CASE WHEN GROUPING(store_id) = 1 THEN 'All Stores' ELSE 'Store ' || store_id::TEXT END AS store_lbl, COUNT(*) AS cnt FROM sales.orders WHERE store_id IN (1, 2) GROUP BY ROLLUP (store_id) ORDER BY store_id NULLS LAST LIMIT 10;
SELECT CASE WHEN GROUPING(region_id) = 1 THEN 'National' ELSE 'Region ' || region_id::TEXT END AS reg_lbl, COUNT(*) AS cnt FROM stores.stores WHERE region_id IN (1, 2) GROUP BY ROLLUP (region_id) ORDER BY region_id NULLS LAST LIMIT 10;
Solidify your hierarchical subtotal mastery with these 4 production challenges on RetailMart.
sales.orders for stores 1 and 2, group by ROLLUP (store_id, order_status). Confirm output returns exactly 17 rows, totaling 1,480 orders and ₹12.52 crore revenue across store subtotals and the apex grand total.GROUPING() on store_id and order_status to label subtotal rows cleanly ('All Stores', 'All Statuses') without leaving raw NULLs in dashboard output.products.products for supplier_id <= 5, calculate SKU counts and average margins grouped by ROLLUP (supplier_id, brand_id). Verify supplier subtotals and catalog grand total.stores.stores for region_id IN (1, 2, 3), compute store counts and total retail square footage grouped by ROLLUP (region_id, city), labeling NULLs cleanly with GROUPING().Track your progress across the complete 59-session curriculum.
Multi-dimensional cross-tabulations, combinatorial power, and selective grouping sets.