Aggregate functions

From rows to answers

So far every query returned one result row per table row. But many questions want a single number: How many students are there? What is the total stock value? What is the highest salary? An aggregate function (from "aggregate", to gather together) reads a whole set of rows and returns one value.

SELECT COUNT(*) AS all_students, COUNT(marks) AS with_marks, COUNT(DISTINCT city) AS cities
FROM students;
-- 8 | 7 | 5

The three kinds of COUNT

• COUNT(*) counts rows, whatever is in them. Use it for "how many orders", "how many students".
• COUNT(column) counts rows where that column is not NULL. COUNT(email) on SHOP customers gives 4, because two have no email.
• COUNT(DISTINCT column) counts different non-NULL values. COUNT(DISTINCT dept_id) in COMPANY is 4: departments 1 to 4, and Deepak's NULL is not counted.

SUM and expressions

An aggregate can work on an expression, not just a column. The value of all stock is the sum of price × stock over every product:

SELECT SUM(price * stock) AS stock_value FROM products;
-- 169430

How NULLs are skipped

SUM, AVG, MIN, MAX and COUNT(column) all ignore NULLs. For SUM that is usually what you want. For AVG it needs thought: AVG(marks) divides the total by the number of students who HAVE marks, not by all students.

SELECT ROUND(AVG(marks), 2) AS avg_marks,
       ROUND(AVG(COALESCE(marks, 0)), 2) AS avg_as_zero
FROM students;
-- 72 | 63

MIN and MAX are not only for numbers

MIN and MAX work on text and dates too. MIN(name) is the first name in alphabetical order; MAX(hire_date) is the most recent hire, because ISO dates sort correctly as text (module 3).

SELECT MIN(salary) AS lowest, MAX(salary) AS highest,
       MIN(hire_date) AS first_hire, MAX(hire_date) AS latest_hire
FROM employees;
-- 45000 | 250000 | 2015-04-01 | 2024-06-01

Aggregates over no rows

If WHERE removes every row, COUNT gives 0 but SUM, AVG, MIN and MAX give NULL. SELECT SUM(price) FROM products WHERE category = 'Toys' returns NULL, not 0. Wrap it in COALESCE(SUM(price), 0) when a report must show 0.

You cannot mix rows and aggregates (yet)

SELECT name, MAX(salary) FROM employees asks for many names and one maximum in the same row. Standard SQL rejects it; SQLite runs it and happens to show the name from the row with the maximum, which hides the problem. To get "who earns the most", sort and LIMIT (module 2) or use a subquery (module 6). To get one answer per group, use GROUP BY, the next topic.

Common mistakes

• Using COUNT(column) when you mean COUNT(*), and getting a smaller number because of NULLs.
• Forgetting that AVG skips NULLs. Decide whether missing should count as 0, and use COALESCE if so.
• Showing NULL instead of 0 when no rows match. Use COALESCE(SUM(x), 0).
• Writing COUNT(DISTINCT *): it is not valid. Name the column: COUNT(DISTINCT city).
• Expecting AVG to round. It does not; add ROUND(AVG(x), 2).