Sql · Aggregate and GROUP BY
Having
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Introduction to HAVING Clause
The
HAVING
clause is used to filter groups in SQL. While the
WHERE
clause filters rows before they are grouped, the
HAVING
clause filters groups after the
GROUP BY
clause has been applied.
The syntax for using
HAVING
is:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition_on_aggregate;
Let's see the
HAVING
clause in action. Suppose we want to find Codémon types that have an average power greater than 75:
SELECT type, AVG(power) as avg_power
FROM codemon
GROUP BY type
HAVING AVG(power) > 75;
This query first groups the Codémons by their type, then calculates the average power for each group. The
HAVING
clause then filters out any groups where the average power is not greater than 75.
HAVING vs WHERE
Remember the key difference:
WHERE
filters individual rows before grouping, while
HAVING
filters groups after they've been created by GROUP BY. You can only use aggregate functions like COUNT(), AVG(), SUM(), etc. in the HAVING clause, not in the WHERE clause.
Exercise 2 — When to Use HAVING
In which case do you need to use
HAVING
instead of
WHERE
?
Options
• To find types of Codémons where the average power is greater than 70
• To find Codémons with power greater than 70
• To find Codémons of type 'water'
• To find Codémons captured before '2023-01-01'
Hint : ```HAVING``` is used to filter after grouping. If you're using aggregate functions like ```AVG()``` with ```GROUP BY```, and want to filter based on that result, use ```HAVING```. All other cases use ```WHERE```.
Exercise 3 — Using HAVING with COUNT
A common use of
HAVING
is with the
COUNT()
function to filter groups based on the number of rows in each group.
For example, we can find Codémon powers that have more than 1 Codémon in our database:
SELECT power, COUNT(*) as codemon_count
FROM codemon
GROUP BY power
HAVING COUNT(*) > 1;
This query counts the number of Codémons for each power and only keeps groups where the count is greater than 1.
Similarly to
WHERE
, we can also use
HAVING
with multiple conditions.
Column Aliases in HAVING
In many databases, you cannot use column aliases defined in the SELECT clause within the HAVING clause. It's safer to repeat the aggregate function in the HAVING clause rather than using the alias.
Exercise 4 — Complete the HAVING Query
Complete the SQL query to find Codémon types where the total number of wins is greater than 15.
SELECT type, ____(wins) as total_wins
FROM codemon
____ BY type
____ ____(wins) > 15;
Options: SUM AVG GROUP ORDER HAVING WHERE SUM
Hint : Think about which aggregate function is needed to find the total, and which clause filters groups rather than individual rows.
Exercise 5 — WHERE vs HAVING: Key Differences
Understanding the difference between
WHERE
and
HAVING
is crucial for effective SQL querying. Here are the key differences:
1.
WHERE
filters individual rows before grouping.
2.
HAVING
filters groups after the
GROUP BY
clause.
3.
WHERE
cannot use aggregate functions.
4.
HAVING
is typically used with aggregate functions.
5.
WHERE
is processed before
GROUP BY
.
6.
HAVING
is processed after
GROUP BY
.
Let's see an example that demonstrates both in the same query:
SELECT type, AVG(power) as avg_power
FROM codemon
WHERE level > 15
GROUP BY type
HAVING AVG(power) > 70;
In this query:
1. The
WHERE
clause filters out individual Codémons with level ≤ 15 before grouping.
2. The remaining rows are grouped by type.
3. The
HAVING
clause then filters out groups where the average power is ≤ 70.
Order of Execution
SQL clauses are executed in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Understanding this order helps in writing correct queries.
Exercise 6 — Match Filters with Appropriate Clauses
Match each filtering requirement with the appropriate SQL clause (
WHERE
or
HAVING
):
Items to match
• Filter Codémons with power greater than 75
• Filter types with an average power greater than 75
• Filter types that have more than 2 Codémons
• Filter out Codémons with level less than 15
Definitions
• WHERE
• HAVING
• HAVING
• WHERE
Exercise 7 — Analyzing a HAVING Query
Given the following query, what will it return?
SELECT type, SUM(wins) as total_wins
FROM codemon
GROUP BY type
HAVING SUM(wins) > SUM(losses);
Options
• Codémon types where the total wins are greater than total losses
• Codémons that have more wins than losses
• Types that have at least one Codémon with more wins than losses
• The query will return an error because HAVING cannot compare two aggregate functions
Hint : Think about what SUM does in a GROUP BY context, and what the HAVING clause is filtering based on.
Exercise 8 — Complex Filtering with HAVING
The
HAVING
clause can contain complex conditions using AND, OR, and NOT, just like the WHERE clause. This allows for sophisticated filtering of groups.
Let's find Codémon types that have both a high average power (>70) AND a high average level (>15):
SELECT type, AVG(power) as avg_power, AVG(level) as avg_level
FROM codemon
GROUP BY type
HAVING AVG(power) > 70 AND AVG(level) > 15;
The
HAVING
clause can also use mathematical expressions. Here we find types where the average power is at least twice the average level:
SELECT type, AVG(power) as avg_power, AVG(level) as avg_level
FROM codemon
GROUP BY type
HAVING AVG(power) >= 2 * AVG(level);
HAVING with NULL Values
Be careful when using aggregate functions with columns that might contain NULL values. Most aggregate functions ignore NULLs, but they can still affect your results. Consider using COUNT(*) vs COUNT(column) carefully.
Exercise 9 — Complete the Complex HAVING Query
Complete the SQL query to find Codémon types that have an average level greater than 18 OR a total number of wins greater than 7, but only include types with more than 1 Codémon.
SELECT type, AVG(level) as avg_level, SUM(wins) as total_wins, COUNT(*) as codemon_count
FROM codemon
GROUP BY type
____ (____(level) > 18 OR ____(wins) > 7) AND ____(*) > 1;
Options: HAVING WHERE AVG SUM COUNT MAX
Hint : Think about which clause filters grouped data and which aggregate functions you need for average level, total wins, and counting Codémons.
Exercise 10 — Match Queries with Their Purpose
Match each SQL query with what it accomplishes:
Items to match
• SELECT type FROM codemon GROUP BY type HAVING COUNT(*) > 1;
• SELECT type FROM codemon WHERE level > 15 GROUP BY type;
• SELECT type FROM codemon GROUP BY type HAVING AVG(power) > AVG(level);
• SELECT type FROM codemon WHERE wins > losses GROUP BY type HAVING SUM(wins) > 10;
Definitions
• Types that have more than one Codémon
• Types of Codémons with level greater than 15
• Types where average power exceeds average level
• Types where Codémons have more wins than losses and total wins exceed 10
Exercise 11 — Build a Complete SQL Query
Complete the SQL query to find all Codémon types where the average health is greater than 100, but only consider Codémons with a level of at least 15, and only include types that have at least 2 Codémons meeting these criteria.
SELECT type, AVG(health) as avg_health, COUNT(*) as codemon_count
FROM codemon
____ level >= 15
____ BY type
____ AVG(health) > 100 AND COUNT(*) ____ 2;
Options: WHERE HAVING GROUP ORDER >= > ==
Hint : Think about the proper order of SQL clauses and which conditions apply to individual rows vs groups.