You have a products table with columns category, price, and stock. Which query shows the average price and total stock for each category, but only for categories with more than 10 products?
hard📝 Application Q15 of 15
SQL - GROUP BY and HAVING
You have a products table with columns category, price, and stock. Which query shows the average price and total stock for each category, but only for categories with more than 10 products?
ASELECT category, AVG(price), SUM(stock) FROM products GROUP BY category HAVING COUNT(*) > 10;
BSELECT category, AVG(price), SUM(stock) FROM products WHERE COUNT(*) > 10 GROUP BY category;
CSELECT category, AVG(price), SUM(stock) FROM products GROUP BY category WHERE COUNT(*) > 10;
DSELECT category, AVG(price), SUM(stock) FROM products HAVING COUNT(*) > 10 GROUP BY category;
Step-by-Step Solution
Solution:
Step 1: Understand filtering groups with HAVING
To filter groups based on aggregate conditions, use HAVING after GROUP BY.
Step 2: Analyze each option's clause order
Only SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category HAVING COUNT(*) > 10; correctly uses GROUP BY then HAVING. Using WHERE with COUNT(*) is invalid (WHERE processes rows before grouping), and placing HAVING before GROUP BY or incorrect clause ordering is invalid syntax.
Final Answer:
SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category HAVING COUNT(*) > 10; -> Option A
Quick Check:
Use HAVING to filter groups after GROUP BY [OK]
Quick Trick:Use HAVING after GROUP BY to filter groups by aggregate [OK]
Common Mistakes:
MISTAKES
Using WHERE to filter aggregate results
Placing HAVING before GROUP BY
Confusing clause order in SQL
Master "GROUP BY and HAVING" in SQL
9 interactive learning modes - each teaches the same concept differently
