Question

In SQL, which clause is used to filter the results of an aggregate function?

• A. WHERE
• B. GROUP BY
• C. HAVING
• D. ORDER BY

Hint:

{WHERE} filters rows before grouping, while {HAVING} filters results after aggregate functions are applied.

Answer 1

The Correct Option is C

Step 1: Understanding the Question:
The question asks for the specific SQL clause used to apply a filter based on the output of an aggregate function like COUNT(), SUM(), AVG(), etc.

Step 2: Detailed Explanation:
In SQL, filtering can happen at two different stages of a query's execution:

Filtering before grouping (WHERE clause):
The WHERE clause is used to filter individual rows from a table before any grouping or aggregation is performed. You cannot use an aggregate function in a WHERE clause because the aggregation has not happened yet.
Example:
SELECT * FROM sales WHERE amount > 100;

Filtering after grouping (HAVING clause):
The HAVING clause is specifically designed to filter groups after the GROUP BY clause has created them and the aggregate functions have been computed. This is where you place conditions on aggregate results.
Example:
SELECT department, SUM(salary) 
FROM employees 
GROUP BY department 
HAVING SUM(salary) > 500000; 

The GROUP BY clause is used to create the groups, and the ORDER BY clause is used to sort the final result set. Neither is used for filtering aggregates.

Step 3: Final Answer:
The SQL clause used to filter the results of an aggregate function is HAVING.