SQL Quiz: Grouping Sets
10 questions · exam conditions
0:00
Grouping SetsQuestion 1 of 10

Consider this query:

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

Assume region and product contain no stored NULL values.

Which description gives an equivalent conceptual implementation using separate grouped queries?

Use a region-grouped query and a product-grouped query, align the absent column with NULL, and combine them with UNION ALL.
Use a region-grouped query and a product-grouped query, retain both original columns, and combine them with an inner join.
Use one query grouped by both region and product, replace either column with NULL, and apply DISTINCT afterward.
Use one ungrouped grand-total query for each column, label the totals separately, and combine them with UNION ALL.
← Back to quizzes

SQL Quiz

SQL Quiz: Grouping Sets

Practice Grouping Sets in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Grouping Sets, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

Consider this query:

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

Assume region and product contain no stored NULL values.

Which description gives an equivalent conceptual implementation using separate grouped queries?

  1. Use a region-grouped query and a product-grouped query, align the absent column with NULL, and combine them with UNION ALL. (correct answer)
  2. Use a region-grouped query and a product-grouped query, retain both original columns, and combine them with an inner join.
  3. Use one query grouped by both region and product, replace either column with NULL, and apply DISTINCT afterward.
  4. Use one ungrouped grand-total query for each column, label the totals separately, and combine them with UNION ALL.
Explanation: When you encounter GROUPING SETS in SQL, think of it as a shortcut for running multiple GROUP BY queries and stacking their results. Each set in the parentheses defines one grouping level, and the final output combines all of them into a single result set. Here, GROUPING SETS ((region), (product)) produces two subtotal groups: one that aggregates SUM(amount) per region, and one that aggregates it per product. In the region group, there is no meaningful product value, so SQL fills that column with NULL as a placeholder — and vice versa for the product group. The two result sets are then union-stacked, preserving every row from both groups. That's exactly what answer A describes: two separate grouped queries where the absent column is represented as NULL, combined with UNION ALL. UNION ALL is critical here rather than plain UNION, because UNION ALL retains all rows without deduplication, faithfully replicating every aggregated row from both groupings. B is wrong because an inner join would combine rows horizontally, merging region totals with product totals into paired columns — that's not what GROUPING SETS does at all. C is wrong because GROUPING SETS ((region), (product)) does not group by both columns simultaneously; using a single GROUP BY region, product with a DISTINCT afterthought produces entirely different granularity. D is wrong because ungrouped grand-total queries collapse everything into one row per query, losing the per-region and per-product breakdowns entirely. A useful study tip: whenever you see GROUPING SETS, mentally replace it with multiple GROUP BY queries joined by UNION ALL, filling missing grouping columns with NULL. That mental model will keep the semantics clear on exam questions.

Question 2

After filtering, East has purchases from customers C1 and C2, while West has purchases from customers C2 and C3. A customer may purchase in more than one region. The report executes:

SELECT region, COUNT(DISTINCT customer_id) AS customers FROM sales GROUP BY GROUPING SETS ((region), ());

Which set of counts is returned for the two region rows and the grand-total row?

  1. East 22, West 22, and grand total 44 because the regional counts are added
  2. East 22, West 22, and grand total 22 because only customers present in every region remain
  3. East 11, West 11, and grand total 33 because shared customers are removed regionally
  4. East 22, West 22, and grand total 33 because C2 is counted once overall (correct answer)
Explanation: When you see GROUPING SETS combined with COUNT(DISTINCT ...), you need to think carefully about two separate scopes: how duplicates are eliminated within each grouping level versus across the entire dataset. GROUPING SETS ((region), ()) produces three rows: one for East, one for West, and one grand-total row (the empty set ()). For each row, COUNT(DISTINCT customer_id) counts unique customers within that row's scope independently. East has C1 and C2 → 22 distinct customers. West has C2 and C3 → 22 distinct customers. The grand-total row sees all customers across the entire table — C1, C2, and C3 — and counts them as 33 distinct values, because C2 appears in both regions but is deduplicated once overall. That makes D the correct answer: East 22, West 22, grand total 33. Choice A incorrectly treats the grand total as a simple sum of the regional counts (2+2=42 + 2 = 4), but DISTINCT never double-counts the same customer — C2 is one person regardless of how many regions they appear in. Choice B claims the grand total reflects only customers present in every region (like an intersection), which would be an INTERSECT operation, not how GROUPING SETS works at all. Choice C invents the idea that shared customers are removed from regional counts, which has no basis in SQL — C2 is legitimately counted in both East and West independently. A useful rule of thumb: with COUNT(DISTINCT ...) and GROUPING SETS, each grouping level performs its own independent deduplication. The grand-total row is not derived from the subtotals — it rescans the data with no grouping filter applied.

Question 3

A report must return three aggregation levels from sales: totals by both region and product, subtotals by region, and one grand total. It must not return product-only subtotals.

Which GROUP BY clause produces exactly the required aggregation levels?

  1. GROUP BY GROUPING SETS ((region, product), (region), ()) (correct answer)
  2. GROUP BY GROUPING SETS ((region, product), (product), ())
  3. GROUP BY GROUPING SETS ((region), (product), ())
  4. GROUP BY GROUPING SETS ((region, product), (region, product), ())
Explanation: When working with GROUPING SETS, your job is to explicitly list every combination of columns you want aggregated — SQL produces exactly one output row per set you specify, nothing more, nothing less. Map the business requirements directly to sets before evaluating any answer choice. The report needs three levels: (region, product) for the most granular rows, (region) for regional subtotals, and () for the grand total. Option A lists exactly (region, product), (region), () — a perfect one-to-one match with all three requirements. That makes A the correct answer. Option B replaces (region) with (product), giving you product-only subtotals instead of region subtotals. The passage explicitly says product-only subtotals must not appear, so B violates the requirement directly. Option C drops the combined (region, product) grouping entirely and instead uses separate (region) and (product) sets — you'd get region subtotals and product subtotals, but never a row that breaks down both dimensions together, missing the most detailed level the report requires. Option D duplicates (region, product) twice, which produces redundant rows at that level and still never generates region-only subtotals, so it both over-counts one level and omits another. A useful strategy: treat each requirement in the problem as a checklist item and verify that every set in GROUPING SETS maps to exactly one item — no extras, no omissions. Remember that () always means the grand total (no grouping columns), and that GROUPING SETS gives you surgical control, so a missing or swapped set is never harmless.

Question 4

A query groups sales independently by region and by product:

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

Which statement correctly describes the effect of the HAVING clause?

  1. It keeps a region or product aggregate row only when that row's own total exceeds 100. (correct answer)
  2. It keeps both aggregation levels only when the grand total across all sales exceeds 100.
  3. It filters source rows whose individual amount exceeds 100 before either grouping set is evaluated.
  4. It keeps product aggregates above 100, but region aggregates are unaffected because they are listed first.
Explanation: Whenever you see HAVING combined with GROUPING SETS, ask yourself: at what level does the filter apply? The key insight is that HAVING always operates row by row on the already-grouped output — it does not know or care how many grouping sets produced those rows. GROUPING SETS ((region), (product)) tells SQL to produce two independent sets of aggregate rows: one set summarizing totals per region, and another summarizing totals per product. Each of those rows has its own SUM(amount). After aggregation, HAVING SUM(amount) > 100 then inspects each row individually and discards any row whose total is 100 or below — regardless of whether that row came from the region grouping or the product grouping. That's exactly what A describes, making it correct. B is wrong because HAVING never waits to evaluate some grand total across all rows; it filters each aggregate row on its own merit. A single grand total isn't even computed here — there's no empty grouping set () in the query. C confuses HAVING with WHERE. WHERE filters individual source rows before grouping; HAVING filters after grouping. Writing HAVING SUM(amount) > 100 cannot possibly inspect raw amount values row by row. D invents a precedence rule that doesn't exist. SQL applies HAVING uniformly to every aggregate row regardless of the order grouping sets are listed. A reliable study tip: mentally replace HAVING with "filter the grouped output rows." If the condition touches an aggregate function, it belongs in HAVING and applies equally to every resulting aggregate row, no matter how many grouping sets created them.

Question 5

The sales table may contain actual NULL values in both region and product. A query uses GROUPING SETS ((region, product), (region), ()) and selects GROUPING(region) as gr and GROUPING(product) as gp.

Which condition identifies only the grand-total row without confusing stored NULL values with aggregation placeholders?

  1. region IS NULL AND product IS NULL
  2. gr = 0 AND gp = 0
  3. gr = 1 AND gp = 1 (correct answer)
  4. gr = 0 AND gp = 1
Explanation: When working with GROUPING SETS, the key challenge is distinguishing rows where a column is NULL because the data actually contains NULL versus rows where NULL appears as an aggregation placeholder. The GROUPING() function exists precisely to solve this problem — it returns 1 when the column is being suppressed for that aggregation level, and 0 when the column is genuinely participating in the grouping (even if its value happens to be NULL). The grand-total row, produced by the empty set () in your GROUPING SETS, aggregates across everything — both region and product are suppressed. That means GROUPING(region) returns 1 and GROUPING(product) returns 1. So the correct condition is gr = 1 AND gp = 1, which is C. A (region IS NULL AND product IS NULL) is the classic trap here. If your table contains actual NULL values in both columns, those real-data rows will also appear as NULL in the result, making this condition ambiguous — you cannot distinguish a stored NULL from a grouping placeholder using IS NULL alone. B (gr = 0 AND gp = 0) identifies the opposite situation: rows where both columns are actively participating in grouping, which corresponds to the (region, product) subtotal level — not the grand total. D (gr = 0 AND gp = 1) identifies rows where region is active but product is suppressed, matching the (region) subtotal level. As a study tip, remember: whenever GROUPING SETS are involved, always use GROUPING() flags rather than IS NULL checks to identify aggregation levels — NULL in results is ambiguous, but GROUPING() never is.

Question 6

After its WHERE clause is evaluated, a query has no remaining source rows. It then executes:

SELECT region, COUNT(*) AS row_count FROM sales WHERE sale_date > CURRENT_DATE GROUP BY GROUPING SETS ((region), ());

Which result should be expected under standard aggregate semantics?

  1. No rows, because neither the region grouping set nor the empty grouping set has source data.
  2. One grand-total row with region shown as NULL and row_count equal to 00. (correct answer)
  3. One region-level row with region shown as NULL and row_count equal to 00.
  4. Two rows with region shown as NULL, one for each grouping set, both having count 00.
Explanation: When working with GROUPING SETS, you need to understand that each grouping set is evaluated independently and always produces output — even when the source data is empty. GROUPING SETS ((region), ()) tells the engine to produce two logical groupings: one partitioned by region, and one grand total (the empty set ()). When no rows survive the WHERE clause, the (region) grouping set produces no rows because there are no distinct region values to group. However, the empty grouping set () is special — it represents an unconditional grand total across all source rows, and by standard SQL semantics, aggregating zero rows still produces exactly one row. COUNT(*) over an empty set returns 00, and since no region is being grouped, the region column appears as NULL. This gives you a single grand-total row: NULL | 0. That makes B the correct answer. A is wrong because it incorrectly assumes the empty grouping set also produces no output. The grand-total grouping set always emits one row, regardless of whether any source rows exist — this mirrors how SELECT COUNT(*) FROM sales WHERE 1=0 returns 00, not an empty result. C is wrong in its label: a row with region = NULL and row_count = 0 is correct, but calling it a "region-level row" misidentifies it. That output comes from the grand-total (empty) grouping set, not the (region) partition. D is wrong because the (region) grouping set with no source data contributes zero rows — not one — leaving only the single grand-total row. Remember: the empty grouping set () behaves like a mandatory aggregate — it always produces exactly one output row, even over empty input.

Question 7

The expenses table has two departments. Department D1 has total expenses of 7070, and department D2 has total expenses of 3030. Neither department value is NULL.

What logical result is produced by this query?

SELECT department, SUM(cost) AS total_cost FROM expenses GROUP BY GROUPING SETS ((department), ());

  1. Two rows: D1 with 7070 and D2 with 3030; the empty grouping set adds no row.
  2. Three rows: D1 with 7070, D2 with 3030, and NULL with 100100. (correct answer)
  3. Three rows: D1 with 100100, D2 with 100100, and NULL with 100100.
  4. Four rows: D1 with 7070, D2 with 3030, plus one total row for each department.
Explanation: Whenever you see GROUPING SETS in SQL, think of it as a shorthand for running multiple GROUP BY queries and stacking their results with UNION ALL. Each set inside the parentheses defines one level of aggregation, and each level produces its own set of rows in the output. Here, GROUPING SETS ((department), ()) specifies two levels: (department) groups rows by department, and () — the empty set — produces a single grand total across all rows. So the query generates three rows: D1 with 7070, D2 with 3030, and one grand-total row. Because the grand-total row has no department to display, SQL represents it as NULL in the department column with a summed value of 70+30=10070 + 30 = 100. That makes B correct. A is wrong because it assumes the empty grouping set () produces nothing. It always produces exactly one row — the grand total — which is the entire point of including it. C is wrong because it confuses GROUPING SETS with a cross-join or window function. Each department group aggregates only its own rows, not all rows. D1 sums to 7070, not 100100. D is wrong because it imagines one total row per department, which would require something like ROLLUP combined with per-department subtotals — not what () does. The empty set always produces exactly one row, not one per group. A useful memory aid: every entry inside GROUPING SETS (...) maps to exactly one aggregation pass. Count the entries, and you know the minimum number of output row groups.

Question 8

A sales table contains rows for exactly four distinct region-product pairs: East-A, East-B, West-A, and West-C. Thus, there are two distinct regions and three distinct products. Assume no grouping column contains NULL.

How many result rows are produced by the following query?

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

  1. 99 rows: four pair totals, two region totals, and three product totals
  2. 1010 rows: four pair totals, two region totals, three product totals, and one grand total (correct answer)
  3. 1212 rows: six possible pairs, two region totals, three product totals, and one grand total
  4. 1414 rows: four pair totals plus all region, product, and grand-total combinations
Explanation: When you see GROUPING SETS in SQL, your job is to count the distinct grouping specifications listed, then figure out how many output rows each one produces given the actual data. In this query, GROUPING SETS ((region, product), (region), (product), ()) defines four separate grouping levels. Think of each set as its own mini GROUP BY:
  • (region, product) → groups by each unique region-product pair. Since exactly four pairs exist (East-A, East-B, West-A, West-C), this produces 4 rows.
  • (region) → groups by distinct regions: East and West → 2 rows.
  • (product) → groups by distinct products: A, B, and C → 3 rows.
  • () → the grand total with no grouping → 1 row.
Adding these up: 4+2+3+1=104 + 2 + 3 + 1 = \mathbf{10} rows total, confirming B is correct. Choice A gets the first three grouping sets right but forgets the empty set (), which always produces exactly one grand-total row — a common oversight. Choice C inflates the pair count to 6, as if every region-product combination existed (2 regions × 3 products). But the table only contains four actual pairs — West-B and East-C don't exist, so they produce no rows. Choice D arrives at 14 with no coherent logic; it conflates the number of grouping sets with some multiplicative combination that doesn't correspond to how GROUPING SETS works. Study tip: Always count rows based on actual distinct values in the data, not the theoretical maximum. GROUPING SETS never invents rows for combinations absent from the table.

Question 9

Assume the SQL implementation supports standard grouping-set output columns. The following query is executed:

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

What is the expected role of region and product in the result?

  1. Every result row contains both values because each selected column appears in at least one grouping set.
  2. Each result row represents a region-product pair because the selected columns are implicitly combined for grouping.
  3. The query is invalid because every selected nonaggregate column must occur in every individual grouping set.
  4. Region-total rows show a placeholder for product, while product-total rows show a placeholder for region. (correct answer)
Explanation: When you see GROUPING SETS in SQL, think about how it generates separate aggregation passes — one for each set listed in parentheses. The query here produces two distinct groups of rows: one set aggregated by region, and another aggregated by product. These are not combined; they stack on top of each other in the result. This is exactly what makes D correct. For rows produced by the (region) grouping set, SQL has no product value to report — so it fills that column with NULL as a placeholder. Likewise, rows produced by the (product) grouping set have NULL in the region column. Each row carries a meaningful value in one grouping column and a NULL in the other, reflecting which aggregation produced it. Answer A is wrong because it assumes both columns are always populated. In standard GROUPING SETS behavior, a column not part of a particular set's grouping is explicitly set to NULL — it does not carry forward from another set. Answer B describes something closer to GROUP BY region, product, which would produce one row per unique region-product combination — a fundamentally different operation. Answer C represents a common misunderstanding: GROUPING SETS deliberately relaxes the rule that every non-aggregate SELECT column must appear in the GROUP BY. The columns can appear in some sets but not all, with NULL filling the gaps. A good study tip: whenever you see GROUPING SETS, mentally simulate each set independently. Ask yourself which columns are "active" in each pass — those get real values, everything else gets NULL.

Question 10

A location report needs totals by (country, city), subtotals by country, and one grand total. It must exclude city-only subtotals, even when the same city name occurs in multiple countries.

Which clause most directly and explicitly specifies the required levels?

  1. GROUP BY GROUPING SETS ((country), (city), ())
  2. GROUP BY GROUPING SETS ((country, city), (city), ())
  3. GROUP BY GROUPING SETS ((country, city), (country), ()) (correct answer)
  4. GROUP BY GROUPING SETS ((country, city), (country), (city), ())
Explanation: When working with GROUPING SETS, think of each set in parentheses as one specific aggregation level you want in your result. Your job is to list exactly the combinations needed — no more, no less. The question requires three levels: detail rows grouped by (country, city), subtotals by (country) alone, and one grand total represented by () (an empty set). Notice the requirement explicitly excludes city-only subtotals, because a bare (city) grouping would collapse rows from different countries that share a city name — producing misleading or unwanted aggregations. Option C — GROUP BY GROUPING SETS ((country, city), (country), ()) — matches all three requirements exactly: full detail, country subtotals, and a grand total. That makes C the correct answer. Option A uses (country), (city), (), which gives country subtotals and city-only subtotals but omits the (country, city) detail rows entirely. You'd lose the granular breakdown the report needs. Option B uses (country, city), (city), (), which includes the (city) grouping the question explicitly says to exclude, and it drops the (country) subtotals — a double mistake. Option D lists all four sets: (country, city), (country), (city), (). While it includes everything correct, it also adds the forbidden (city) subtotals, making it non-compliant with the spec even though it feels "complete." A useful tip: with GROUPING SETS, treat the requirement like a checklist — include only the grouping levels asked for. Extra sets aren't harmless; they produce rows you don't want.