Sql · Aggregate and GROUP BY
Count, Sum, avg, min and max
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Introduction to Aggregate Functions
Aggregate functions in SQL allow you to perform calculations on a set of values and return a single result. They are used to summarize data from multiple rows.
The most common aggregate functions are:
- COUNT(): Counts the number of rows or non-NULL values
- SUM(): Calculates the sum of numeric values
- AVG(): Calculates the average of numeric values
- MIN(): Finds the minimum value
- MAX(): Finds the maximum value
Aggregate Functions vs Regular Functions
The key difference is that aggregate functions operate on multiple rows and return a single value, while regular functions operate on each row individually.
Exercise 2 — The COUNT() Function
The COUNT() function is used to count rows in a result set. It's one of the most commonly used aggregate functions in SQL.
There are two main ways to use COUNT():
1. ***COUNT(*): Counts all rows, including NULL values and duplicates
2. COUNT(column)***: Counts only non-NULL values in the specified column
SELECT COUNT(*) AS total_codemons FROM codemon;
Let's see the difference between COUNT(*) and COUNT(column) when there are NULL values:
SELECT
COUNT(*) AS total_rows,
COUNT(health) AS non_null_health
FROM codemon;
You can also use COUNT() with a WHERE clause to count specific rows:
SELECT COUNT(*) AS powerful_codemons
FROM codemon
WHERE power > 70;
COUNT(*) vs COUNT(column)
Always remember that COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column. This difference is important when your data contains NULL values.
Exercise 3 — Understanding COUNT()
Given our Codémon table, which query will count the total number of Codémons that have recorded losses (non-NULL values in the losses column)?
Options
• ```SELECT COUNT(losses) FROM codemon;```
• ```SELECT COUNT(*) FROM codemon;```
• ```SELECT SUM(losses) FROM codemon;```
• ```SELECT COUNT(*) FROM codemon WHERE losses IS NULL;```
Hint : Think about how COUNT(*) differs from COUNT(column) when it comes to handling NULL values.
Exercise 4 — The SUM() Function
The SUM() function calculates the total sum of a numeric column. It adds up all the non-NULL values in the specified column.
SELECT SUM(column_name) FROM table_name;
Let's find the total power of all Codémons:
SELECT SUM(power) AS total_power FROM codemon;
You can also use SUM() with a WHERE clause to calculate the sum for specific rows:
SELECT SUM(wins) AS total_fire_water_wins
FROM codemon
WHERE type = 'water' OR type = 'fire';
NULL Values and SUM()
SUM() ignores NULL values in its calculation. If all values are NULL, SUM() will return NULL. Also, SUM() can only be used with numeric columns - using it with text columns will result in an error.
Exercise 5 — Using the SUM() Function
Complete the SQL query to find the total number of losses for all Codémons with a level greater than 15.
SELECT ____(losses) AS total_losses
FROM codemon
____ level > 15;
Options: SUM COUNT WHERE HAVING AVG GROUP BY
Hint : Think about which function adds up values from multiple rows, and which clause allows you to filter rows before aggregation.
Exercise 6 — The AVG() Function
The AVG() function calculates the average (mean) value of a numeric column. It adds up all non-NULL values and divides by the count of non-NULL values.
SELECT AVG(column_name) FROM table_name;
Let's calculate the average power of all Codémons:
SELECT AVG(power) AS average_power FROM codemon;
As with other aggregate functions, you can use AVG() with a WHERE clause:
SELECT AVG(health) AS avg_health_of_powerful_codemons
FROM codemon
WHERE power > 70;
NULL Values and AVG()
AVG() ignores NULL values in both the sum and the count. For example, AVG(5, 10, NULL) is calculated as (5+10)/2 = 7.5, not (5+10+0)/3 = 5. This is an important distinction!
Exercise 7 — AVG() Function and NULL Values
Looking at our Codémon table, if we run:
SELECT AVG(losses) FROM codemon;
How does the AVG() function handle the NULL value in the losses column?
Options
• It ignores the NULL value in both the sum and the count of values
• It treats NULL as 0 in the sum, but ignores it in the count
• It returns NULL because there is a NULL value in the column
• It counts NULL as 0 in both the sum and the count
Hint : Think about how AVG() handles NULL values. Is a NULL treated as 0, or is it completely excluded from the calculation?
Exercise 8 — The MIN() and MAX() Functions
The MIN() and MAX() functions find the minimum and maximum values in a column, respectively. They work with numeric, text, and date columns.
SELECT MIN(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;
Let's find the lowest and highest power levels among our Codémons:
SELECT
MIN(power) AS lowest_power,
MAX(power) AS highest_power
FROM codemon;
MIN() and MAX() also work with text columns, where they follow alphabetical ordering:
SELECT
MIN(name) AS first_alphabetically,
MAX(name) AS last_alphabetically
FROM codemon;
Like other aggregate functions, you can use MIN() and MAX() with WHERE clauses:
SELECT MAX(health) AS highest_fire_health
FROM codemon
WHERE type = 'fire';
NULL Values and MIN()/MAX()
MIN() and MAX() ignore NULL values. If all values in a column are NULL, both functions will return NULL.
Exercise 9 — Match Aggregate Functions with Their Purposes
Match each aggregate function with its correct purpose:
Items to match
• COUNT(*)
• SUM(column)
• AVG(column)
• MAX(column)
Definitions
• Counts all rows in a table
• Adds up all non-NULL values in a column
• Calculates the mean of non-NULL values
• Finds the highest value in a column
Exercise 10 — Using MIN() and MAX()
Complete the SQL query to find both the minimum and maximum number of wins among all Codémons.
SELECT
____(wins) AS fewest_wins,
____(wins) AS most_wins
FROM codemon;
Options: MIN MAX COUNT SUM AVG SMALLEST
Hint : Think about which functions find the smallest and largest values in a set of data.
Exercise 11 — Combining Aggregate Functions
You can combine aggregate functions to get multiple statistics in a single query. This is useful for summarizing data in various ways.
Let's find various statistics for Codémons with power greater than 70:
SELECT
COUNT(*) AS count,
AVG(level) AS avg_level,
MIN(health) AS min_health,
MAX(health) AS max_health,
SUM(wins) AS total_wins
FROM codemon;
You can use also use WHERE to filter rows before aggregation. For example, to find statistics for Codémons with a level greater than 15.
SELECT
COUNT(*) AS count,
AVG(power) AS avg_power
FROM codemon
WHERE level > 15;
Exercise 12 — Using Multiple Aggregate Functions
Which query shows the average, minimum, and maximum health of all Codémons in one result?
Options
• ```SELECT AVG(health), MIN(health), MAX(health) FROM codemon;```
• ```SELECT health, health, health FROM codemon;```
• ```SELECT AVG(health) FROM codemon GROUP BY health;```
• ```Run 3 separate SELECTs, one for each value.```
Hint : You can use multiple aggregate functions in one SELECT to get all values in a single row.
Exercise 13 — NULL Handling in Aggregate Functions
Match each statement about NULL handling in aggregate functions with its correct status (True or False):
Items to match
• COUNT(*) includes rows with NULL values
• COUNT(column) counts NULL values in that column
• AVG() treats NULL values as zero in calculations
• MIN() and MAX() ignore NULL values
Definitions
• True
• False
• False
• True
Exercise 14 — Practical Aggregate Calculations
If we want to calculate the win-loss ratio (wins divided by losses) for all Codémons, which query would be correct?
Options
• ```SELECT SUM(wins) / SUM(losses) AS win_loss_ratio FROM codemon;```
• ```SELECT AVG(wins / losses) AS win_loss_ratio FROM codemon;```
• ```SELECT MAX(wins) / MAX(losses) AS win_loss_ratio FROM codemon;```
• ```SELECT COUNT(wins) / COUNT(losses) AS win_loss_ratio FROM codemon;```
Hint : Think about whether you want to calculate the ratio of totals or the average of individual ratios. Also consider how NULL values would affect each approach.
Exercise 15 — Complete the Statistics Query
Complete the SQL query to find the number of Codémons, their average level, and total wins for Codémons with health greater than 100.
SELECT
____(*) AS codemon_count,
____(level) AS average_level,
____(wins) AS total_wins
FROM codemon
____ health > 100;
Options: COUNT AVG SUM MIN MAX WHERE HAVING
Hint : Think about which function counts rows, which calculates an average, which adds up values, and which clause filters rows before aggregation.