You have a table orders with columns customer_id, order_date, and total. You want to find the average order total per customer but only for customers who have placed more than 3 orders. Which query correctly achieves this?
ASELECT customer_id, AVG(total) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3;
BSELECT customer_id, AVG(total) FROM orders WHERE COUNT(*) > 3 GROUP BY customer_id;
CSELECT customer_id, AVG(total) FROM orders GROUP BY customer_id WHERE COUNT(*) > 3;
DSELECT customer_id, AVG(total) FROM orders HAVING COUNT(*) > 3 GROUP BY customer_id;
Step-by-Step Solution
Solution:
Step 1: Use GROUP BY to group orders by customer_id
This groups all orders per customer to calculate aggregates.
Step 2: Use HAVING to filter groups with more than 3 orders
HAVING filters groups after aggregation; WHERE cannot filter aggregates.
Final Answer:
SELECT customer_id, AVG(total) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3; -> Option A
Quick Check:
HAVING filters groups, WHERE filters rows [OK]
Quick Trick:Use HAVING to filter groups after GROUP BY [OK]
Common Mistakes:
MISTAKES
Using WHERE to filter aggregated counts
Placing HAVING before GROUP BY
Not filtering groups at all
Master "GROUP BY and HAVING" in SQL
9 interactive learning modes - each teaches the same concept differently
