Which query correctly uses the FILTER clause to achieve this?
hard📝 Application Q8 of 15
PostgreSQL - Aggregate Functions and GROUP BY
You want to find the total sales amount and the total sales amount only for orders with status 'completed' from the sales table grouped by region. Which query correctly uses the FILTER clause to achieve this?
ASELECT region, SUM(amount) FILTER (WHERE status = 'completed') AS total_sales, SUM(amount) AS completed_sales FROM sales GROUP BY region;
BSELECT region, SUM(amount) AS total_sales, SUM(amount) FILTER (WHERE status = 'completed') AS completed_sales FROM sales GROUP BY region;
CSELECT region, SUM(amount) AS total_sales, COUNT(amount) FILTER (WHERE status = 'completed') AS completed_sales FROM sales GROUP BY region;
DSELECT region, SUM(amount) FILTER (WHERE status = 'completed') AS total_sales, COUNT(amount) AS completed_sales FROM sales GROUP BY region;
Step-by-Step Solution
Solution:
Step 1: Identify total sales and filtered sales
Total sales is SUM(amount) for all rows; completed sales is SUM(amount) filtered by status = 'completed'.
Step 2: Match query to requirements
SELECT region, SUM(amount) AS total_sales, SUM(amount) FILTER (WHERE status = 'completed') AS completed_sales FROM sales GROUP BY region; correctly assigns total_sales as SUM(amount) and completed_sales as SUM(amount) FILTER (WHERE status = 'completed').
Final Answer:
SELECT region, SUM(amount) AS total_sales, SUM(amount) FILTER (WHERE status = 'completed') AS completed_sales FROM sales GROUP BY region; -> Option B
Quick Check:
FILTER clause filters inside aggregate, total sum is unfiltered [OK]
Quick Trick:Put FILTER only on the aggregate needing condition [OK]
Common Mistakes:
Swapping filtered and unfiltered aggregates
Using COUNT instead of SUM for amounts
Misplacing FILTER clause
Master "Aggregate Functions and GROUP BY" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
