GROUPING SETS for multiple groupings in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GROUPING SETS with two grouping sets

Consider a sales table with columns region, product, and sales_amount. What is the output of this query?

SELECT region, product, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((region), (product));

PostgreSQL

SELECT region, product, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY GROUPING SETS ((region), (product));

A Rows grouped by region only and rows grouped by product only, with NULLs in the other column

B Rows grouped by region and product together and also total sum without grouping

C Rows grouped by both region and product combined only

D Rows grouped by region only, product only, and also by both region and product combined

💡 Hint

GROUPING SETS allows you to specify multiple groupings separately.

The query groups rows separately by region and by product. Each grouping set produces rows with NULL in the other column.

🧠 Conceptual

intermediate

Understanding GROUPING SETS behavior

Which statement best describes what GROUPING SETS ((a), (b)) does in a GROUP BY clause?

A It groups rows by column <code>a</code> only and ignores column <code>b</code>.

B It groups rows by the combination of columns <code>a</code> and <code>b</code> only.

C It groups rows by column <code>a</code> and separately by column <code>b</code>, producing two sets of grouped results.

D It groups rows by column <code>b</code> only and ignores column <code>a</code>.

💡 Hint

Think about how GROUPING SETS lets you specify multiple groupings.

GROUPING SETS ((a), (b)) produces two separate groupings: one by a and one by b.

📝 Syntax

advanced

Identify the syntax error in GROUPING SETS query

Which option contains a syntax error in the GROUP BY clause using GROUPING SETS?

SELECT department, role, COUNT(*) FROM employees
GROUP BY GROUPING SETS (department, (role));

A GROUP BY GROUPING SETS ((department), (role))

B GROUP BY GROUPING SETS (department, role)

C ))elor( ,)tnemtraped(( STES GNIPUORG YB PUORG

D GROUP BY GROUPING SETS ((department, role))

💡 Hint

GROUPING SETS requires each grouping set to be enclosed in parentheses.

Option B lacks parentheses around each grouping set, causing a syntax error.

❓ optimization

advanced

Optimizing multiple groupings with GROUPING SETS

You want to get total sales grouped by region, product, and also the grand total. Which GROUP BY clause is the most efficient and correct?

A GROUP BY GROUPING SETS ((region), (product), ())

B GROUP BY region, product WITH ROLLUP

C GROUP BY region, product, ()

D GROUP BY GROUPING SETS ((region, product), ())

💡 Hint

Think about how to include multiple groupings and the grand total.

Option A explicitly lists grouping sets for region, product, and the empty set for grand total.

🔧 Debug

expert

Why does this GROUPING SETS query produce unexpected NULLs?

Given the query:

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

Why might some rows have NULL in both region and product columns?

A Because the SUM aggregation returns NULL when no rows match.

B Because GROUPING SETS automatically adds an empty grouping set causing NULLs.

C Because the columns <code>region</code> and <code>product</code> contain NULL values in the data.

D Because the query is missing the empty grouping set <code>()</code> which produces the grand total row with NULLs.

💡 Hint

Think about what produces the grand total row with NULLs in grouping columns.

Only the empty grouping set () produces a row with NULLs in all grouping columns representing the grand total.

Practice

1. What is the main purpose of using GROUPING SETS in a PostgreSQL query?

easy

A. To group data by multiple combinations in a single query

B. To filter rows based on conditions

C. To join multiple tables together

D. To sort the result set by multiple columns

Solution

1. Step 1: Understand GROUPING SETS functionality

   GROUPING SETS allows grouping by multiple sets of columns in one query, combining results.

2. Step 2: Compare with other options

   Filtering rows is done by WHERE, joining tables by JOIN, sorting by ORDER BY, not GROUPING SETS.

3. Final Answer:

   To group data by multiple combinations in a single query -> Option A

4. Quick Check:

   GROUPING SETS = multiple groupings in one query [OK]

Hint: GROUPING SETS groups multiple ways at once [OK]

Common Mistakes:

• Confusing GROUPING SETS with WHERE filtering
• Thinking GROUPING SETS sorts data
• Mixing GROUPING SETS with JOIN operations

2. Which of the following is the correct syntax to use GROUPING SETS in a PostgreSQL query?

easy

A. SELECT col1, col2 FROM table ORDER BY GROUPING SETS (col1, col2);

B. SELECT col1, col2 FROM table WHERE GROUPING SETS (col1, col2);

C. SELECT col1, col2 FROM table HAVING GROUPING SETS (col1, col2);

D. SELECT col1, col2 FROM table GROUP BY GROUPING SETS ((col1), (col2));

Solution

1. Step 1: Identify correct GROUP BY usage

   GROUPING SETS is used inside GROUP BY clause with sets of columns in parentheses.

2. Step 2: Check other clauses

   WHERE, ORDER BY, HAVING do not support GROUPING SETS syntax.

3. Final Answer:

   SELECT col1, col2 FROM table GROUP BY GROUPING SETS ((col1), (col2)); -> Option D

4. Quick Check:

   GROUPING SETS goes inside GROUP BY [OK]

Hint: GROUPING SETS always follows GROUP BY keyword [OK]

Common Mistakes:

• Placing GROUPING SETS in WHERE clause
• Using GROUPING SETS with ORDER BY
• Confusing HAVING with GROUP BY for GROUPING SETS

3. Given the table sales(product, region, amount), what will be the output of this query?

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

medium

A. Sum of amounts grouped by product only

B. Sum of amounts grouped by product and region combined

C. Sum of amounts grouped by product and sum grouped by region in one result

D. Syntax error due to wrong GROUPING SETS usage

Solution

1. Step 1: Understand GROUPING SETS ((product), (region))

   This groups data twice: once by product alone, once by region alone, producing two sets of grouped sums.

2. Step 2: Analyze output rows

   Result will have rows with product and NULL region, and rows with region and NULL product, each showing sum of amounts.

3. Final Answer:

   Sum of amounts grouped by product and sum grouped by region in one result -> Option C

4. Quick Check:

   GROUPING SETS gives multiple groupings combined [OK]

Hint: GROUPING SETS returns all specified groupings combined [OK]

Common Mistakes:

• Expecting grouping by both columns together
• Thinking it returns only one grouping
• Assuming syntax error without checking GROUP BY clause

4. Identify the error in this query:

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

medium

A. Missing WHERE clause for filtering

B. GROUPING SETS requires each grouping to be enclosed in parentheses

C. GROUP BY cannot have multiple columns

D. SUM() cannot be used with GROUPING SETS

Solution

1. Step 1: Check GROUPING SETS syntax

   GROUPING SETS expects a list of grouping sets, each enclosed in parentheses, e.g., ((product), (region)).

2. Step 2: Identify the error

   The query uses (product, region) without extra parentheses, which is invalid syntax for GROUPING SETS.

3. Final Answer:

   GROUPING SETS requires each grouping to be enclosed in parentheses -> Option B

4. Quick Check:

   GROUPING SETS syntax needs nested parentheses [OK]

Hint: Each grouping set must be in its own parentheses [OK]

Common Mistakes:

• Omitting parentheses around grouping sets
• Thinking SUM() is incompatible with GROUPING SETS
• Confusing GROUP BY multiple columns with GROUPING SETS syntax

5. You want to create a report showing total sales grouped by product, by region, and by both product and region together. Which GROUPING SETS clause achieves this in one query?

hard

A. GROUP BY GROUPING SETS ((product), (region), (product, region))

B. GROUP BY product, region

C. GROUP BY GROUPING SETS (product, region)

D. GROUP BY CUBE (product, region)

Solution

1. Step 1: Understand required groupings

   We need three groupings: by product alone, by region alone, and by both together.

2. Step 2: Match with GROUPING SETS syntax

   GROUPING SETS ((product), (region), (product, region)) explicitly lists all three groupings.

3. Step 3: Evaluate other options

   GROUP BY product, region groups by both columns together only; C is invalid syntax; D (CUBE) includes all combinations but is not GROUPING SETS.

4. Final Answer:

   GROUP BY GROUPING SETS ((product), (region), (product, region)) -> Option A

5. Quick Check:

   Explicit sets list all needed groupings [OK]

Hint: List all groupings inside GROUPING SETS parentheses [OK]

Common Mistakes:

• Using GROUP BY without GROUPING SETS for multiple groupings
• Confusing CUBE with GROUPING SETS
• Incorrect syntax without nested parentheses