GROUPING SETS for multiple groupings in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the total sales grouped by region and product using GROUPING SETS.

PostgreSQL

SELECT region, product, SUM(sales) FROM sales_data GROUP BY [1];

Drag options to blanks, or click blank then click option'

AGROUPING SETS ((region), (product))

Bregion, product

CROLLUP (region, product)

DCUBE (region, product)

Attempts:

3 left

💡 Hint

Common Mistakes

Using just column names without GROUPING SETS.

Using ROLLUP or CUBE which group differently.

The GROUPING SETS clause allows grouping by multiple sets of columns separately. Here, grouping by region and product individually is done by GROUPING SETS ((region), (product)).

2fill in blank

medium

Complete the code to group sales by region and product, and also by region alone using GROUPING SETS.

PostgreSQL

SELECT region, product, SUM(sales) FROM sales_data GROUP BY [1];

Drag options to blanks, or click blank then click option'

AGROUPING SETS ((region, product), (region))

BGROUPING SETS ((region), (product))

CROLLUP (region, product)

DGROUP BY region, product

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROLLUP which adds subtotals but not exactly these groupings.

Using GROUP BY with columns directly without GROUPING SETS.

This GROUPING SETS clause groups by both (region, product) and (region) separately, giving totals per product per region and totals per region.

3fill in blank

hard

Fix the error in the GROUP BY clause to correctly use GROUPING SETS for grouping by product and year.

PostgreSQL

SELECT product, year, SUM(amount) FROM sales GROUP BY [1];

Drag options to blanks, or click blank then click option'

AGROUPING SETS ((product, year))

BGROUP BY product, year

CGROUPING SETS (product, year)

DGROUPING SETS ((product), (year))

Attempts:

3 left

💡 Hint

Common Mistakes

Not using parentheses around each grouping set.

Using GROUP BY without GROUPING SETS for multiple groupings.

GROUPING SETS requires each grouping set to be enclosed in parentheses. So to group by product and year separately, use GROUPING SETS ((product), (year)).

4fill in blank

hard

Fill both blanks to group sales by region and product, and also by product alone using GROUPING SETS.

PostgreSQL

SELECT region, product, SUM(sales) FROM sales_data GROUP BY [1], [2];

Drag options to blanks, or click blank then click option'

AGROUPING SETS ((region, product), (product))

BROLLUP (region, product)

CGROUP BY region, product

DCUBE (region, product)

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to put multiple grouping clauses separated by commas.

Using ROLLUP or CUBE instead of GROUPING SETS.

GROUPING SETS can be used in the GROUP BY clause as a single argument. Here, both blanks should be filled with the same GROUPING SETS clause to form a valid GROUP BY.

5fill in blank

hard

Fill all three blanks to select region, product, and year with total sales grouped by these combinations using GROUPING SETS.

PostgreSQL

SELECT [1], [2], [3], SUM(sales) FROM sales_data GROUP BY GROUPING SETS ((region, product), (region, year), (product, year));

Drag options to blanks, or click blank then click option'

Aregion

Bproduct

Cyear

Dsales

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting the aggregated column without SUM or other aggregation.

Missing one of the grouping columns in SELECT.

The SELECT clause must list the columns used in the GROUPING SETS: region, product, and year. The sales column is aggregated, so it is not selected directly.