Question

Which SQL query correctly filters out groups with a total sales amount greater than 10,000?

• A. SELECT CustomerID, SUM(Sales) FROM Orders WHERE SUM(Sales) $>$ 10000 GROUP BY CustomerID;
• B. SELECT CustomerID, SUM(Sales) FROM Orders GROUP BY CustomerID ORDER BY SUM(Sales) $>$ 10000;
• C. SELECT CustomerID, SUM(Sales) FROM Orders GROUP BY CustomerID HAVING SUM(Sales) $>$ 10000;
• D. SELECT CustomerID, SUM(Sales) FROM Orders WHERE Sales $>$ 10000 GROUP BY CustomerID;

Hint:

Remember the execution order of an SQL query:
FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ HAVING $\rightarrow$ SELECT $\rightarrow$ ORDER BY.
Since WHERE executes before GROUP BY, it can never evaluate aggregate functions.

Answer 1

The Correct Option is C

•

Step 1: Understanding the Question:
The question asks us to identify the correct SQL query that aggregates order data by CustomerID and applies a filter on the aggregated sum of sales.
Specifically, we want to retrieve only those customer groups where the total sum of their sales exceeds 10,000.

•

Step 2: Key Formula or Approach:
In SQL, row-level filtering and group-level filtering are handled by two distinct clauses:
1. The WHERE clause is used to filter individual rows before any grouping occurs.
2. The HAVING clause is used to filter group results after the GROUP BY clause has aggregated the rows.
Aggregate functions like SUM(), AVG(), and COUNT() cannot be evaluated inside a WHERE clause.

•

Step 3: Detailed Explanation:
Let us evaluate each of the given SQL query options:
- Option (A) attempts to place SUM(Sales) $>$ 10000 inside the WHERE clause. This will result in a syntax error because aggregate values are computed after grouping, whereas the WHERE clause executes before grouping.
- Option (B) uses ORDER BY SUM(Sales) $>$ 10000. This performs a sorting operation based on a boolean condition rather than filtering out records.
- Option (C) groups the rows by CustomerID first, computes the aggregate SUM(Sales) for each group, and then correctly applies the HAVING SUM(Sales) $>$ 10000 filter. This is the syntactically correct and standard method to filter grouped data.
- Option (D) filters individual sales transactions that are greater than 10,000 before grouping, which is not the same as filtering groups with a total combined sales sum greater than 10,000.
Thus, option (C) is the only valid query that achieves the desired result.

•

Step 4: Final Answer:
The correct query is option (C), which employs the HAVING clause alongside the GROUP BY statement.