HAVING clause in MySQL - Step-by-Step Execution

Concept Flow - HAVING clause

Start with a table

↓

GROUP BY groups rows

↓

Aggregate functions calculate

↓

HAVING filters groups

↓

Return filtered groups

The HAVING clause filters groups created by GROUP BY after aggregation, unlike WHERE which filters rows before grouping.

Execution Sample

MySQL

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

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

Execution Table

Step	Action	Intermediate Result	HAVING Condition Check	Output
1	Group rows by department	Groups: Sales(3), HR(2), IT(4)	N/A	No output yet
2	Calculate COUNT(*) for each group	Sales=3, HR=2, IT=4	N/A	No output yet
3	Apply HAVING emp_count > 2	Check Sales: 3 > 2	True	Include Sales
4	Apply HAVING emp_count > 2	Check HR: 2 > 2	False	Exclude HR
5	Apply HAVING emp_count > 2	Check IT: 4 > 2	True	Include IT
6	Return filtered groups	Sales and IT groups	N/A	Output rows for Sales and IT

💡 All groups checked; only those with emp_count > 2 are returned

Variable Tracker

Variable	Start	After Grouping	After Aggregation	After HAVING Filter
groups	empty	Sales(3), HR(2), IT(4)	Sales=3, HR=2, IT=4	Sales=3, IT=4
emp_count	N/A	N/A	3, 2, 4	3, 4

Key Moments - 2 Insights

Why doesn't HAVING filter individual rows like WHERE?

HAVING works after grouping and aggregation, filtering groups, not individual rows. See execution_table rows 1-3 where grouping and aggregation happen before HAVING filters.

Can HAVING use column names not in GROUP BY?

Yes, HAVING can use aggregate results like emp_count which are not in GROUP BY. This is shown in execution_table row 3 where emp_count is used in HAVING.

Visual Quiz - 3 Questions

Look at the execution_table, which department is excluded by the HAVING clause?

A HR

B IT

C Sales

D None

💡 Hint

Check the HAVING Condition Check column in rows 4 and 5

At which step are the groups created?

A Step 2

B Step 3

C Step 1

D Step 6

💡 Hint

Look at the Action column describing grouping

If the HAVING condition changed to emp_count > 3, which groups would be included?

A Sales and IT

B Only IT

C Only Sales

D All groups

💡 Hint

Refer to emp_count values in variable_tracker after aggregation

Concept Snapshot

HAVING clause filters groups after aggregation
Used with GROUP BY to restrict grouped results
Syntax: SELECT ... GROUP BY ... HAVING condition
HAVING can use aggregate functions like COUNT(), SUM()
Different from WHERE which filters rows before grouping

Full Transcript

The HAVING clause is used in SQL to filter groups created by the GROUP BY clause. First, rows are grouped by a column, then aggregate functions like COUNT calculate values per group. HAVING applies a condition to these aggregated values to decide which groups to keep. Unlike WHERE, which filters individual rows before grouping, HAVING filters after grouping. For example, a query can count employees per department and use HAVING to show only departments with more than two employees. The execution steps show grouping, aggregation, then filtering by HAVING. Variables track groups and counts before and after filtering. Common confusions include why HAVING filters groups not rows, and that HAVING can use aggregate results. Visual quiz questions help reinforce these ideas by referencing the execution steps and variable changes.

Practice

1. What is the main purpose of the HAVING clause in SQL?

easy

A. To filter groups after aggregation

B. To filter rows before grouping

C. To sort the result set

D. To join two tables

Solution

1. Step 1: Understand filtering in SQL

   The WHERE clause filters rows before grouping, but HAVING filters after groups are formed.

2. Step 2: Identify the role of HAVING

   HAVING is used to filter groups based on aggregate conditions like COUNT or SUM.

3. Final Answer:

   To filter groups after aggregation -> Option A

4. Quick Check:

   HAVING filters groups, not rows [OK]

Hint: HAVING filters groups after aggregation, WHERE filters rows before [OK]

Common Mistakes:

• Confusing HAVING with WHERE clause
• Using HAVING without GROUP BY
• Thinking HAVING sorts data

2. Which of the following is the correct syntax to use HAVING with GROUP BY in MySQL?

easy

A. SELECT col1 FROM table HAVING COUNT(col2) > 5 GROUP BY col1;

B. SELECT col1, COUNT(col2) FROM table GROUP BY col1 HAVING COUNT(col2) > 5;

C. SELECT col1, COUNT(col2) FROM table HAVING COUNT(col2) > 5;

D. SELECT col1, COUNT(col2) FROM table GROUP BY col1 WHERE COUNT(col2) > 5;

Solution

1. Step 1: Recall correct order of clauses

   In SQL, GROUP BY comes before HAVING. The correct order is SELECT -> FROM -> GROUP BY -> HAVING.

2. Step 2: Check each option's syntax

   SELECT col1, COUNT(col2) FROM table GROUP BY col1 HAVING COUNT(col2) > 5; correctly places GROUP BY before HAVING and includes aggregate function in HAVING condition.

3. Final Answer:

   SELECT col1, COUNT(col2) FROM table GROUP BY col1 HAVING COUNT(col2) > 5; -> Option B

4. Quick Check:

   GROUP BY before HAVING [OK]

Hint: GROUP BY must come before HAVING in SQL syntax [OK]

Common Mistakes:

• Placing HAVING before GROUP BY
• Using WHERE with aggregate functions
• Omitting GROUP BY when using HAVING

3. Given the table sales with columns region and amount, what will this query return?

SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region HAVING total_sales > 1000;

medium

A. All regions with sales less than or equal to 1000

B. Syntax error because alias can't be used in HAVING

C. All regions with total sales greater than 1000

D. Empty result because HAVING filters rows

Solution

1. Step 1: Understand the query logic

   The query groups rows by region and sums amount as total_sales.

2. Step 2: Apply HAVING condition

   It filters groups where total_sales is greater than 1000. Using alias in HAVING is allowed in MySQL.

3. Final Answer:

   All regions with total sales greater than 1000 -> Option C

4. Quick Check:

   HAVING filters groups by aggregate result [OK]

Hint: HAVING filters groups by aggregate values like SUM [OK]

Common Mistakes:

• Thinking HAVING filters rows, not groups
• Assuming alias can't be used in HAVING in MySQL
• Confusing HAVING with WHERE

4. Identify the error in this query:

SELECT department, COUNT(*) FROM employees HAVING COUNT(*) > 10;

medium

A. Missing GROUP BY clause before HAVING

B. COUNT(*) cannot be used in HAVING

C. HAVING should be replaced with WHERE

D. No error, query is correct

Solution

1. Step 1: Check for GROUP BY usage

   The query uses HAVING with COUNT(*) but does not group by department. HAVING requires GROUP BY to define groups.

2. Step 2: Understand HAVING requirements

   HAVING filters groups after aggregation. Without GROUP BY, groups are not defined, causing an error.

3. Final Answer:

   Missing GROUP BY clause before HAVING -> Option A

4. Quick Check:

   HAVING needs GROUP BY to filter groups [OK]

Hint: Always use GROUP BY before HAVING to define groups [OK]

Common Mistakes:

• Using HAVING without GROUP BY
• Replacing HAVING with WHERE incorrectly
• Assuming COUNT(*) can't be in HAVING

5. You have a products table with columns category, price, and stock. Write a query to find categories where the average price is above 50 and total stock is at least 100.
Which query correctly uses HAVING to achieve this?

hard

A. SELECT category FROM products GROUP BY category WHERE AVG(price) > 50 AND SUM(stock) >= 100;

B. SELECT category FROM products HAVING AVG(price) > 50 AND SUM(stock) >= 100 GROUP BY category;

C. SELECT category FROM products WHERE AVG(price) > 50 AND SUM(stock) >= 100 GROUP BY category;

D. SELECT category FROM products GROUP BY category HAVING AVG(price) > 50 AND SUM(stock) >= 100;

Solution

1. Step 1: Recall correct clause order

   The correct order is SELECT -> FROM -> GROUP BY -> HAVING. HAVING filters groups based on aggregate conditions.

2. Step 2: Analyze each option

   SELECT category FROM products GROUP BY category HAVING AVG(price) > 50 AND SUM(stock) >= 100; correctly groups by category and uses HAVING with aggregate functions AVG(price) and SUM(stock).

3. Final Answer:

   SELECT category FROM products GROUP BY category HAVING AVG(price) > 50 AND SUM(stock) >= 100; -> Option D

4. Quick Check:

   HAVING filters grouped data with aggregate conditions [OK]

Hint: Use HAVING after GROUP BY for aggregate filters [OK]

Common Mistakes:

• Placing HAVING before GROUP BY
• Using WHERE with aggregate functions
• Omitting GROUP BY when using HAVING