Supporting reference and preserved earlier practice. Open the current classroom lesson →

SUPPORTING SQL REFERENCE · LESSON 13

Lesson 13: Filter rows, then filter groups

Learn advanced aggregate techniques with the HAVING clause to filter grouped results.

Practice this lesson · 3 exercises ↓Related class labs ↓

Practice database: Buildings & employees. Recovered teaching snapshot. Explore the live schema below before choosing your columns.

Read the explanation, then use the practice section below. Generic syntax and illustrative tables explain the idea; the exercise schema shows the exact names available in Buildings & employees.

Our queries are getting fairly complex, but we have nearly introduced all the important parts of aSELECT query. One thing that you might have noticed is that if the GROUP BY clause is executed after the WHERE clause (which filters the rows which are to be grouped), then how exactly do we filter the grouped rows?

Luckily, SQL allows us to do this by adding an additional HAVING clause which is used specifically with the GROUP BY clause to allow us to filter grouped rows from the result set.

Select query with HAVING constraint

SELECT

group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, ...

FROM

mytable

WHERE

condition

GROUP BY

column

HAVING

group_condition;

The HAVING clause constraints are written the same way as the WHERE clause constraints, and are applied to the grouped rows. With our examples, this might not seem like a particularly useful construct, but if you imagine data with millions of rows with different properties, being able to apply additional constraints is often necessary to quickly make sense of the data.

Did you know?

WHERE filters individual rows. HAVING tests aggregate groups. For these exercises, write GROUP BY with HAVING, and use WHERE when you need to filter employees before calculating a total.

Key Differences: WHERE vs HAVING

WHERE: Filters individual rows before grouping
HAVING: Filters groups after aggregation
WHERE: Cannot use aggregate functions
HAVING: Can use aggregate functions (COUNT, SUM, etc.)
Execution Order: WHERE → GROUP BY → HAVING → ORDER BY

Exercise

For this exercise, you are going to dive deeper into Employee data at the film studio. Think about the different clauses you want to apply for each task.

Try a worked example

SELECT COUNT(*) as Number_of_artists
FROM employees
WHERE role = 'Artist';

Read the query, predict its result, then inspect the rows or schema it produces.

Open example in playground ↗

TRY IT YOURSELF

Lecture 7: Queries with Aggregates (Pt. 2)

Buildings & employees · Summarize and compare

Recovered teaching snapshot. Explore the live schema below before choosing your columns.

Opening your saved work…

YOUR LESSON DATABASE

Tables, keys & sample rows

0 tables · Updates after your query

PK = primary key · FK = declared foreign key. Expand a table to see its columns and the first five stored rows.

Loading schema…

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Query results

Run a query to explore. Check my answer checks the selected exercise.

Read-only practice · One statement at a time · Ctrl / ⌘ + Enter runs your query