After this lesson you can answer whole-table questions with one query: how many employees are there, what is the total payroll, who was hired first, what is the average salary? These questions all collapse many rows into a single answer, and SQL does it with five aggregate functions.
All examples run on the sample company database, tested on SQLite.
From many rows to one
A normal function such as UPPER works on one row and gives one value back, so a table of 20 rows yields 20 results. An aggregate function works on a whole set of rows and returns one value for the set. Used without GROUP BY, the set is every row that passes the WHERE filter, and the result is a single row.
SELECT COUNT(*) AS people,
SUM(salary) AS total_payroll,
AVG(salary) AS average_salary,
MIN(salary) AS lowest,
MAX(salary) AS highest
FROM employees;+--------+---------------+----------------+--------+---------+
| people | total_payroll | average_salary | lowest | highest |
+--------+---------------+----------------+--------+---------+
| 20 | 1801000 | 90050.0 | 50000 | 180000 |
+--------+---------------+----------------+--------+---------+Each function has a simple job:
COUNTcounts rows or values.SUMadds numbers.AVGcalculates the mean, which is the sum divided by the count of values.MINandMAXfind the smallest and largest value.
WHERE still comes first. It decides which rows join the set, and the aggregate then summarises only those:
SELECT COUNT(*) AS engineers,
SUM(salary) AS engineering_payroll
FROM employees
WHERE department_id = 1;+-----------+---------------------+
| engineers | engineering_payroll |
+-----------+---------------------+
| 6 | 650000 |
+-----------+---------------------+Three ways to count
COUNT has three forms, and the differences matter:
SELECT COUNT(*) AS all_rows,
COUNT(department_id) AS with_department,
COUNT(manager_id) AS with_manager,
COUNT(DISTINCT department_id) AS different_departments
FROM employees;+----------+-----------------+--------------+-----------------------+
| all_rows | with_department | with_manager | different_departments |
+----------+-----------------+--------------+-----------------------+
| 20 | 19 | 14 | 5 |
+----------+-----------------+--------------+-----------------------+COUNT(*) counts rows, whatever they contain. COUNT(column) counts only the rows where that column is not NULL, which is why 20 employees give 19 with a department and 14 with a manager. COUNT(DISTINCT column) counts the different non-NULL values, so the five departments that have staff count once each. The empty department, Research, does not appear in the employees table at all, so it cannot be counted here.
A common slip is to use COUNT(column) when you mean COUNT(*), which quietly drops the rows where the column is empty.
How NULL is treated
SUM, AVG, MIN, MAX and COUNT(column) all skip NULL values. This is usually what you want, but it can surprise you. The projects table has an end_date that is NULL for projects that are still running:
SELECT COUNT(end_date) AS finished,
COUNT(*) AS all_projects
FROM projects;+----------+--------------+
| finished | all_projects |
+----------+--------------+
| 3 | 5 |
+----------+--------------+Three projects have ended and two are ongoing. AVG also skips NULL, so its denominator is the number of non-NULL values, not the number of rows. The lesson on CAST and COALESCE shows how replacing NULL with zero changes that.
When there are no rows
What do the functions return when the filter matches nothing? There is no department 99:
SELECT COUNT(*) AS how_many,
SUM(salary) AS total,
AVG(salary) AS average,
MAX(salary) AS highest
FROM employees
WHERE department_id = 99;+----------+-------+---------+---------+
| how_many | total | average | highest |
+----------+-------+---------+---------+
| 0 | NULL | NULL | NULL |
+----------+-------+---------+---------+COUNT correctly says zero, but SUM, AVG and MAX return NULL, because there is no value to add or compare. If a report must show 0 for an empty total, use COALESCE(SUM(salary), 0).
MIN and MAX on dates and text
MIN and MAX work on anything with an order, not only numbers. On dates they find the earliest and latest, and on text they follow alphabetical order:
SELECT MIN(hire_date) AS first_hire,
MAX(hire_date) AS latest_hire,
MIN(last_name) AS first_alphabetically
FROM employees;+------------+-------------+----------------------+
| first_hire | latest_hire | first_alphabetically |
+------------+-------------+----------------------+
| 2016-04-25 | 2024-06-10 | Bose |
+------------+-------------+----------------------+Counting distinct values and summing project hours
The assignments table records hours people spent on projects. A few useful questions about it:
SELECT SUM(hours) AS total_hours,
COUNT(*) AS assignment_rows,
COUNT(DISTINCT employee_id) AS people_involved,
COUNT(DISTINCT project_id) AS projects_involved
FROM assignments;+-------------+-----------------+-----------------+-------------------+
| total_hours | assignment_rows | people_involved | projects_involved |
+-------------+-----------------+-----------------+-------------------+
| 4180 | 19 | 15 | 5 |
+-------------+-----------------+-----------------+-------------------+There are 19 assignment rows, but only 15 different people, because some people work on more than one project.
Why aggregates cannot go in WHERE
You may be tempted to write WHERE salary > AVG(salary) to find employees paid more than average. It fails. The reason is order: WHERE filters individual rows before any summarising happens, so the average does not exist yet.
SELECT first_name, salary
FROM employees
WHERE salary > AVG(salary);SQLite reports "misuse of aggregate function AVG()", and other databases give a similar error. The fix for now is a subquery, which calculates the average first and hands it to the outer query:
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC
LIMIT 5;+------------+--------+
| first_name | salary |
+------------+--------+
| Meera | 180000 |
| Vikram | 150000 |
| Arjun | 125000 |
| Kavya | 125000 |
| Lata | 115000 |
+------------+--------+The lesson on subqueries covers this in depth, and the lesson on the order a query runs in explains the rule. For filtering on a summary per group, HAVING is the answer, coming shortly.
Aggregates mixed with ordinary columns
Mixing an aggregate with a plain column in the same SELECT is a mistake without GROUP BY. If you want the name of the highest-paid person, writing SELECT first_name, MAX(salary) is ambiguous in standard SQL, because the aggregate gives one row but there are many names. PostgreSQL and SQL Server refuse the query. SQLite allows it and picks a name from the row holding the maximum, which is a SQLite-specific behaviour you should not rely on. The portable form uses a subquery:
SELECT first_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);+------------+--------+
| first_name | salary |
+------------+--------+
| Meera | 180000 |
+------------+--------+Differences between databases
- Average of whole numbers. In SQL Server,
AVGof an integer column returns an integer, which cuts the fraction. Cast first, as inAVG(CAST(salary AS DECIMAL(12,2)))or multiply by1.0. MySQL and PostgreSQL return a decimal. SQLite returns a decimal number too, shown here as90050.0. - Display of results. The same average may appear as
90050,90050.0or90050.0000, depending on the database and the column type. - String joining aggregates. Several databases offer ways to join the values of many rows into one text value, but the name differs (
GROUP_CONCATin MySQL and SQLite,STRING_AGGin PostgreSQL and SQL Server 2017 and later). It is not part of the five core functions.
For more on MySQL specifically, see aggregate functions in MySQL.
Common mistakes
- Using an aggregate in
WHERE. Use a subquery, orHAVINGwithGROUP BY. - Selecting a plain column next to an aggregate without grouping. It is an error on most databases.
- Using
COUNT(column)forCOUNT(*). The rows where the column isNULLvanish from the count. - Forgetting that
SUMof nothing isNULL. Wrap it inCOALESCEwhen you need 0. - Averaging whole numbers on SQL Server and getting a truncated result.
Exercise
Write one query that reports, for the projects table, the number of projects, the total budget, the average budget rounded to a whole number, the smallest budget, and the earliest start date. Name the columns sensibly. Then write a second query listing the projects that have a budget above the average.
Show one possible answer
SELECT COUNT(*) AS projects,
SUM(budget) AS total_budget,
ROUND(AVG(budget)) AS average_budget,
MIN(budget) AS smallest_budget,
MIN(start_date) AS earliest_start
FROM projects;+----------+--------------+----------------+-----------------+----------------+
| projects | total_budget | average_budget | smallest_budget | earliest_start |
+----------+--------------+----------------+-----------------+----------------+
| 5 | 1670000 | 334000.0 | 120000 | 2024-01-15 |
+----------+--------------+----------------+-----------------+----------------+SELECT name, budget
FROM projects
WHERE budget > (SELECT AVG(budget) FROM projects)
ORDER BY budget DESC;+-----------------+--------+
| name | budget |
+-----------------+--------+
| Mobile App | 650000 |
| Customer Portal | 400000 |
+-----------------+--------+Key points
- Aggregate functions collapse many rows into one value. COUNT, SUM, AVG, MIN and MAX are the five you will use constantly.
- COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts different non-NULL values.
- SUM, AVG, MIN and MAX ignore NULL values. On an empty set, COUNT gives 0 but the others give NULL.
- You cannot use an aggregate in WHERE, because WHERE runs before the rows are summarised. Use a subquery now, and HAVING after you meet GROUP BY.