Table Sales(region, amount) has rows (‘N’,100), (‘N’,200), (‘S’,50), (‘S’,400), (‘E’,300). SELECT COUNT(*) FROM (SELECT region FROM Sales WHERE amount >= 100 GROUP BY region HAVING COUNT(*) >= 2) t; What is the result?
Correct answer A. 1
Explanation
WHERE runs before grouping and removes the row ('S',50). The remaining rows form groups N (2 rows), S (1 row) and E (1 row). HAVING COUNT(*) >= 2 then keeps only N, so the outer query counts 1 region. Answering 2 forgets that WHERE has already removed S's small sale before HAVING counts the group.
Answer checked How we write and check questions
Discussion