Write a query to find the total amount spent by each customer, but only include customers who spent more than 1000, and order the results by total spent descending.
hard📝 Application Q8 of 15
SQL - GROUP BY and HAVING
You have a table orders with columns customer_id, order_date, and amount. Write a query to find the total amount spent by each customer, but only include customers who spent more than 1000, and order the results by total spent descending.
ASELECT customer_id, SUM(amount) AS total FROM orders ORDER BY total DESC HAVING SUM(amount) > 1000 GROUP BY customer_id;
BSELECT customer_id, SUM(amount) AS total FROM orders HAVING SUM(amount) > 1000 GROUP BY customer_id ORDER BY total DESC;
CSELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ORDER BY total DESC HAVING SUM(amount) > 1000;
DSELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000 ORDER BY total DESC;
Step-by-Step Solution
Solution:
Step 1: Use GROUP BY and HAVING correctly
GROUP BY groups by customer_id; HAVING filters groups with SUM(amount) > 1000.
Step 2: Place clauses in correct order
HAVING comes after GROUP BY; ORDER BY sorts by alias 'total' descending.
Final Answer:
SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000 ORDER BY total DESC; -> Option D
Quick Check:
GROUP BY, HAVING filter, ORDER BY sort descending [OK]
Quick Trick:HAVING filters groups after GROUP BY; ORDER BY sorts results [OK]
Common Mistakes:
MISTAKES
Placing HAVING before GROUP BY
Using HAVING without GROUP BY
Ordering before filtering groups
Master "GROUP BY and HAVING" in SQL
9 interactive learning modes - each teaches the same concept differently
