Question

Which of the following statement will correctly filter products having an average price of 50?

• A. SELECT Category FROM Products GROUP BY Category HAVING AVG(Price) $>$ 50;
• B. SELECT Category FROM Products GROUP BY WHERE AVG(Price) $>$ 50;
• C. SELECT Category FROM Products HAVING AVG(Price) $>$ 50;
• D. SELECT Category FROM Products GROUP BY AVG(Price) $>$ 50;

Hint:

Always remember: WHERE filters rows before grouping; HAVING filters groups after grouping.
You can never use an aggregate function like AVG() or SUM() in a WHERE clause!

Answer 1

The Correct Option is A

Step 1: Understanding the Question:
The question asks to find the correct SQL query syntax to filter categorized product data based on an aggregated condition, where the average price of the products within each category is greater than 50.

Step 2: Key Rules of SQL Filtering:
- GROUP BY Clause: Used to group rows that have the same values in specified columns into summary rows (e.g., grouping products by category).
- HAVING Clause: Used to filter grouped records or aggregate results. It must be written after the GROUP BY clause.
- WHERE Clause: Used to filter individual raw rows before any grouping is performed. It cannot contain aggregate functions like AVG(), SUM(), or COUNT().

Step 3: Detailed Explanation:
- In the given problem, we need to find the average price of products. This requires aggregating the data using the AVG(Price) function.
- Since we want to perform this filter over a group, we must use the GROUP BY clause on the grouping column (such as Category) and filter the groups using the HAVING clause.
- Let us analyze each option:
- Option (A) SELECT Category FROM Products GROUP BY Category HAVING AVG(Price) > 50; contains the correct syntax by grouping the rows on Category first, and then using the HAVING clause to apply the filter condition on the aggregate function.
- Option (B) contains a syntax error because WHERE cannot directly follow the GROUP BY clause, and WHERE cannot evaluate the aggregate function AVG().
- Option (C) misses the GROUP BY clause entirely, which is semantically incorrect when selecting a grouped attribute and filtering by an aggregate.
- Option (D) incorrectly writes the aggregate condition directly in the GROUP BY clause itself.

Step 4: Final Answer:
The correct query to filter grouped product records on an aggregate condition is represented in Option (A).
Hence, option (A) is the correct choice.