In SQL, what does the HAVING clause do in a SELECT statement that uses GROUP BY?

Introduction / Context:
SQL provides different clauses to filter data at different stages of query processing. The WHERE clause filters individual rows before grouping and aggregation take place. The HAVING clause filters the groups that are produced after GROUP BY and aggregation. Many learners confuse these two clauses because both appear to restrict results, but they operate at different levels of granularity. This question tests your understanding of how the HAVING clause behaves in relation to WHERE and GROUP BY in a standard SQL SELECT statement.

Given Data / Assumptions:

We are working with a relational database that supports SQL and the GROUP BY clause.
The SELECT statement may use aggregate functions such as SUM, COUNT, AVG, MIN, or MAX.
The HAVING clause, if present, appears after GROUP BY in the query.
The question explicitly compares the behaviour of HAVING with the behaviour of WHERE.
No database specific extensions such as vendor specific analytical functions are required for this explanation.

Concept / Approach:
The key concept is to understand the order of logical operations in an SQL SELECT statement. First, the FROM clause identifies the source tables and joins them. Second, the WHERE clause removes individual rows that do not satisfy the specified predicate. Third, the remaining rows are grouped using GROUP BY if that clause is present. Fourth, aggregate functions are computed for each group. Fifth, the HAVING clause evaluates its condition based on each group and its aggregate values, and discards groups that do not meet the condition. Finally, the SELECT list is produced and possibly ordered and limited. By recognizing that HAVING is applied after grouping, we see that it is used to filter entire groups, not individual rows.

Step-by-Step Solution:
Step 1: Recall that WHERE filters rows before any grouping or aggregation. It cannot reference aggregate functions like SUM or COUNT, because those values are not yet computed at the row filtering stage. Step 2: Recall that GROUP BY clusters rows into groups based on one or more columns. Aggregate functions then compute summary information for each group, such as total sales per customer or average salary per department. Step 3: Recognize that HAVING is evaluated after GROUP BY and after aggregate values are available. Therefore, HAVING can safely reference aggregate functions and is used to keep or discard groups based on those aggregate values. Step 4: Apply this logic to the options. The option that states that the HAVING clause acts like a WHERE clause but is used for groups rather than individual rows exactly matches this behaviour and is therefore correct.

Verification / Alternative check:
A simple example can verify the concept. Suppose you want to find departments with total salary above a certain threshold. The query might be: SELECT dept_id, SUM(salary) FROM employees GROUP BY dept_id HAVING SUM(salary) > 100000. Here, WHERE cannot be used to test SUM(salary) because the sum is not defined for a single row. Only after grouping can you compute the sum per department and then apply HAVING to remove groups that do not meet the threshold. This clear separation shows that HAVING is specifically designed to filter groups after aggregation, which confirms the correctness of the chosen option.

Why Other Options Are Wrong:
The option that claims HAVING is used to filter rows rather than columns is incorrect because that is the role of WHERE, not HAVING, and columns are not the main target of row level filters.
The option that claims HAVING filters columns rather than groups is incorrect because columns are chosen in the SELECT list, while HAVING focuses on groups and aggregates, not on individual columns themselves.
The option that claims HAVING acts exactly like WHERE in all situations is incorrect because WHERE cannot reference aggregates in the same way and works on rows before grouping, so their behaviour is different in grouped queries.

Common Pitfalls:
Learners often try to use HAVING in queries that do not use GROUP BY, which is unnecessary when a simple WHERE clause would be more efficient. Another frequent error is trying to use aggregate functions inside WHERE instead of HAVING, leading to syntax errors or misunderstandings about the logical processing order. Some programmers also mistakenly believe that HAVING can be used as a general replacement for WHERE in any query, which can harm performance and readability. Understanding the logical order of SQL evaluation helps avoid these errors and leads to clearer, more maintainable queries.

Final Answer:
The HAVING clause acts like a WHERE clause but is used to filter groups created by GROUP BY rather than individual rows.