An analyst needs every region whose total sales exceed 500000. The query SELECT region, SUM(amount) FROM sales WHERE SUM(amount) > 500000 GROUP BY region; fails. Which corrected query returns the intended result?
Select an answer to reveal the explanation.
Short Explanation
WHERE checks each row at the door; HAVING checks each group after the party's been counted. Since you're filtering on a total, it has to be HAVING, and it goes after GROUP BY. Don't fall for moving the filter to the raw amount; that's a totally different question.
Full Explanation
SQL processes a grouped query in a logical order: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT and ORDER BY. WHERE filters individual rows before any groups exist, so an aggregate such as SUM(amount) cannot appear there, which is why the original query fails. HAVING filters the groups produced by GROUP BY, so HAVING SUM(amount) > 500000 keeps only regions whose total exceeds the threshold. Rewriting the filter as WHERE amount > 500000 is valid syntax but answers a different question: it discards every individual sale of 500000 or less before summing, so a region with many smaller sales is excluded and the remaining totals are wrong. Adding ORDER BY SUM(amount) DESC sorts the regions by total but filters nothing, leaving the analyst to read the cutoff manually. Placing HAVING before GROUP BY reverses the required clause order, and standard SQL rejects that statement as a syntax error. Exam caveat: WHERE and HAVING can be combined, for example WHERE to exclude cancelled orders and HAVING to test the remaining totals, and filtering early with WHERE reduces work. Operational check: run the corrected query, then run the grouped query without HAVING and confirm every excluded region's total is at or below 500000.