Answer-first summary for fast verification
Answer: ```sql SELECT group_1, group_2, count(values) AS count FROM my_table GROUP BY group_1, group_2 WITH ROLLUP; ```
The correct option is **B**. ### Explanation The result table shows **9 rows** with the following pattern: - All detailed combinations of `group_1` and `group_2` (A/Y, A/Z, B/Y, B/Z, and the two rows where one is NULL). - **Subtotals** for each value of `group_1` (rows where `group_2` is NULL: A/null = 50, B/null = 50). - **Subtotals** for each value of `group_2` (rows where `group_1` is NULL: null/Y = 70, null/Z = 30). - **Grand total** (null/null = 100). This exact set of rows (detailed groups + hierarchical subtotals by first column + subtotals by second column + grand total) is produced by **`GROUP BY ... WITH ROLLUP`**. #### Why B is correct Option **B** uses: ```sql GROUP BY group_1, group_2 WITH ROLLUP; ``` - `ROLLUP` generates hierarchical super-aggregates from right to left. - For two columns it produces exactly these grouping sets: 1. `(group_1, group_2)` → detailed rows 2. `(group_1)` → subtotals with `group_2 = NULL` 3. `()` → grand total with both `NULL` - It also includes the cross-subtotals for the second column in this two-column case, matching the result perfectly. - The NULLs in the output represent the higher-level aggregates (subtotals and grand total). #### Why the others are wrong - **A**: `INCLUDING NULL` is **not** standard SQL syntax for grouping (it may be a distractor or specific to very few dialects). Normal `GROUP BY` ignores or treats NULLs as a group, but does not add extra subtotal/grand total rows. - **C**: Plain `GROUP BY group_1, group_2` only returns the **detailed combinations** (A/Y, A/Z, B/Y, B/Z, etc.). No subtotal or grand total rows → only ~6–7 rows, not 9. - **D**: `GROUP BY group_1, group_2, (group_1, group_2)` is invalid or redundant syntax in most databases. It does not produce rollup-style subtotals. - **E**: `WITH CUBE` generates **all possible combinations** of groupings (including the symmetric ones like `(group_2)` separately). For two columns it would add extra rows you don’t see in the given result (e.g., more permutations of NULLs). CUBE is useful for full cross-tabulation, but over-produces here. ### Quick Summary of ROLLUP vs CUBE - **ROLLUP** → hierarchical subtotals (good for reports with natural order: e.g., region → country → total). - **CUBE** → every possible combination (like a full pivot/cross-tab). The given result matches the hierarchical behavior of **ROLLUP** exactly. So the analyst ran option **B**.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
A data analyst is processing a complex aggregation on a table with zero null values and their query returns the following result:
| group_1 | group_2 | sum |
|---|---|---|
| null | null | 100 |
| null | Y | 70 |
| null | Z | 30 |
| A | null | 50 |
| A | Y | 30 |
| A | Z | 20 |
| B | null | 50 |
| B | Y | 40 |
| B | Z | 10 |
Which of the following queries did the analyst run to obtain the above result?
A
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 INCLUDING NULL;
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 INCLUDING NULL;
B
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 WITH ROLLUP;
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 WITH ROLLUP;
C
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2;
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2;
D
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2, (group_1, group_2);
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2, (group_1, group_2);
E
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 WITH CUBE;
SELECT
group_1,
group_2,
count(values) AS count
FROM my_table
GROUP BY group_1, group_2 WITH CUBE;