HAVING clause for filtering groups in SQL - Step-by-Step Execution

Concept Flow - HAVING clause for filtering groups

Start with Table Data

↓

GROUP BY columns

↓

Aggregate functions calculate

↓

Apply HAVING condition on groups

Yes | No↓

Output groups that pass HAVING

↓

END

The HAVING clause filters groups after aggregation, only outputting groups that meet the condition.

Execution Sample

SQL

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

This query counts employees per department and shows only departments with more than 2 employees.

Execution Table

Step	Action	Data State	Result
1	Read all employee rows	employees table with multiple departments	All rows loaded
2	Group rows by department	Groups formed: Sales(3), HR(2), IT(4)	3 groups created
3	Calculate COUNT(*) for each group	Sales=3, HR=2, IT=4	Counts computed
4	Apply HAVING COUNT(*) > 2	Sales=3 (passes), HR=2 (fails), IT=4 (passes)	Sales and IT groups kept
5	Output filtered groups	Departments Sales and IT with counts	Final result with 2 rows
6	End of query	No more steps	Query complete

💡 HAVING filters out HR group because count 2 is not > 2

Variable Tracker

Variable	Start	After Grouping	After Aggregation	After HAVING	Final
Groups	none	Sales(3), HR(2), IT(4)	Sales=3, HR=2, IT=4	Sales=3, IT=4	Sales=3, IT=4
Output Rows	none	none	none	Sales, IT	Sales, IT

Key Moments - 2 Insights

Why doesn't HAVING work like WHERE for filtering individual rows?

HAVING filters after grouping and aggregation, so it works on groups, not individual rows. See execution_table step 4 where groups are filtered after counts are calculated.

What happens if HAVING condition is false for all groups?

No groups are output, resulting in an empty result set. This is shown in execution_table step 4 where HR group is removed because it fails the condition.

Visual Quiz - 3 Questions

Look at the execution_table, what is the count for the IT group after aggregation?

A 3

B 4

C 2

D 5

💡 Hint

Check the 'After Aggregation' column in execution_table row 3 for IT group count.

At which step does the HAVING clause filter out groups?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'Action' column in execution_table to find when HAVING is applied.

If the HAVING condition was changed to COUNT(*) > 4, which groups would remain?

A No groups

B Sales and IT

C Only Sales

D Only IT

💡 Hint

Refer to variable_tracker for group counts and compare with new condition.

Concept Snapshot

HAVING clause filters groups after aggregation.
Syntax: SELECT columns FROM table GROUP BY columns HAVING condition;
HAVING works on aggregated data, unlike WHERE.
Use HAVING to filter groups based on aggregate results.
Groups failing HAVING condition are excluded from output.

Full Transcript

The HAVING clause is used in SQL to filter groups after the data has been grouped and aggregate functions have been calculated. First, the database reads all rows from the table. Then it groups rows by the specified columns. Next, aggregate functions like COUNT calculate values for each group. After that, the HAVING clause filters these groups based on the condition provided. Only groups that meet the condition are included in the final output. For example, if we group employees by department and count them, HAVING COUNT(*) > 2 will only show departments with more than two employees. This differs from WHERE, which filters rows before grouping. If no groups meet the HAVING condition, the result is empty. This step-by-step process helps understand how HAVING works to filter grouped data.