SQL Lesson 11: Queries with Aggregates (Pt. 2)
Learn advanced aggregate techniques with the HAVING clause to filter grouped results.
SQL Lesson 11: Queries with Aggregates (Pt. 2)
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.
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?
If you aren't using the GROUP BY clause, a simple WHERE clause will suffice.
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.
Table: employees
Query results
Exercise 11 — Tasks
- Find the number of Artists in the studio (without a HAVING clause)Hint: You can count the number of artists.
- Find the number of Employees of each role in the studio
- Find the total number of years employed by all Engineers
Solve all tasks to continue to the next lesson.