Restricting group results

1. Restricting group results

Welcome back! In this lesson, we'll cover HAVING which is used to restrict group results.

2. Back to our example

In the last video, we saw that we can use the WHERE clause to exclude rows before dividing them into groups. For example, here all rows with a UnitPrice not equal to 0.99 are filtered out and the remaining rows are grouped by composer. However, what if you wanted to filter out rows once they have been grouped?

3. Limits of WHERE

WHERE can't be used to filter groups and group functions can't be used in WHERE clauses.

4. Limits of WHERE example

For example, if you tried to filter for composers with an average of more than 200000 milliseconds, you would get this syntax error on WHERE.

5. HAVING

Instead, we would have to use the HAVING clause, like this!

6. Restricting group results with HAVING

Here's a diagram to show visually what HAVING does. If we get the maximum song length for each composer, denoted by the dashed lines, we can use HAVING to filter for composers who have a maximum greater than 200,000 millisecond. In this case, only Jimmy Page and Carlos Santana satisfy the condition.

7. HAVING

This is what the SQL query and output would look like.

8. Another example

This is an example that combines a few SQL clauses we've seen. Here we're selecting the composer and their sum of track prices. WHERE is used to only consider tracks of genre 1. HAVING is used to filter for composers with more than 4 tracks of genre 1.

9. Guidelines

As you may have noticed in the previous example, HAVING does not need to use the same aggregate function as the SELECT statement. But an aggregate function does need to be used with the HAVING clause. This is what makes it different from WHERE. Don't forget the order of operations. Rows are first filtered by WHERE and grouped. Then, the aggregate function is applied. Finally, groups that meet the HAVING clause are outputted.

10. Let's practice!

Time to practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.