Given a table sales with columns region, product, and amount, how can you create a list of products sold per region, separated by commas, but only include products with sales above 100?
hard📝 Application Q9 of 15
PostgreSQL - Aggregate Functions and GROUP BY
Given a table sales with columns region, product, and amount, how can you create a list of products sold per region, separated by commas, but only include products with sales above 100?
ASELECT region, STRING_AGG(product, ', ') FROM sales GROUP BY region HAVING amount > 100;
BSELECT region, STRING_AGG(product, ', ') FROM sales WHERE amount > 100 GROUP BY region;
CSELECT region, STRING_AGG(product, ', ') FROM sales GROUP BY region WHERE amount > 100;
DSELECT region, STRING_AGG(product, ', ') FROM sales WHERE amount >= 100 GROUP BY region;
Step-by-Step Solution
Solution:
Step 1: Filter rows with amount > 100 before aggregation
Use WHERE clause to select only rows with amount > 100.
Step 2: Group by region and aggregate product names
Group by region and use STRING_AGG(product, ', ') to list products.
Final Answer:
SELECT region, STRING_AGG(product, ', ') FROM sales WHERE amount > 100 GROUP BY region; -> Option B
Quick Check:
Filter with WHERE, then group and aggregate [OK]
Quick Trick:Filter rows before GROUP BY with WHERE clause [OK]
Common Mistakes:
Using HAVING instead of WHERE for filtering rows
Placing WHERE after GROUP BY
Master "Aggregate Functions and GROUP BY" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
