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.