Aggregate Functions

Everything so far returned one output row per input row. Aggregates do the opposite: they take many rows and return one value.

SQL▶ Run
SELECT COUNT(*)            AS employees,
       ROUND(AVG(salary))  AS average_salary,
       MIN(salary)         AS lowest,
       MAX(salary)         AS highest,
       SUM(salary)         AS payroll
FROM employees;

One row out of ten rows in. That is the whole idea.

The five you will use constantly

MIN and MAX work on text and dates too — MIN(hired_on) is the earliest hire.

SQL▶ Run
SELECT MIN(hired_on) AS first_hire,
       MAX(hired_on) AS latest_hire,
       MIN(first_name) AS alphabetically_first
FROM employees;

COUNT(*) versus COUNT(column)

The distinction that gets asked in interviews and gets reports wrong:

SQL▶ Run
SELECT COUNT(*)                  AS rows_in_table,
       COUNT(salary)             AS salaries_recorded,
       COUNT(dept_id)            AS with_a_department,
       COUNT(DISTINCT dept_id)   AS distinct_departments
FROM employees;

Ten rows, nine salaries, nine departments, three distinct departments. Every number differs, and each answers a different question. COUNT(*) is also the fastest — it never has to look at a column's values.

COUNT(DISTINCT col) counts unique non-NULL values. The NULL department is not a fourth department.

SUM of nothing is NULL — but COUNT of nothing is zero

SQL▶ Run
SELECT COUNT(*)   AS matching_rows,
       SUM(amount) AS total,
       AVG(amount) AS average
FROM orders
WHERE amount > 1000;

No order is over 1000, so COUNT is 0 while SUM and AVG are NULL. This surprises people, and it breaks dashboards: a tile that displays SUM(amount) shows a blank rather than a zero on a quiet day. COALESCE(SUM(amount), 0) is the fix, and it is worth applying by habit to anything a human will read.

Notice too that an aggregate over zero rows still returns one row. SELECT SUM(...) FROM t WHERE <nothing matches> gives you a row containing NULL, not an empty result — which is why "no rows" and "a row of nothing" are different bugs.

Averages lie when values are missing

SQL▶ Run
SELECT COUNT(*)              AS people,
       COUNT(salary)         AS have_a_salary,
       ROUND(AVG(salary), 2) AS avg_reported,
       ROUND(SUM(salary) / COUNT(*), 2) AS avg_if_missing_counted_as_zero
FROM employees;

Two different, defensible numbers from the same data. AVG divides by nine because it skips the NULL; dividing by ten treats the unknown salary as zero. Neither is "correct" in general — you have to know what the missing value means. What is never correct is not noticing that the choice was made for you.

Aggregates cannot be nested or used in WHERE

AVG(SUM(salary)) is an error — an aggregate cannot take another aggregate as input directly. Doing it in two steps needs a subquery or a window function, both covered later.

And WHERE COUNT(*) > 3 is an error too, because WHERE runs before grouping happens. That is HAVING, two lessons from now.

Mixing an aggregate with a plain column

SQL▶ Run
SELECT COUNT(*) AS employees FROM employees;

Adding first_name to that SELECT is a mistake: which of the ten names should sit next to the single count? Standard SQL rejects it. PostgreSQL, SQL Server and Oracle all raise an error. MySQL (outside strict mode) and SQLite instead return an arbitrary row's value, silently — which is worse, because the query appears to work. The fix is GROUP BY, which is the next lesson.

Common mistakes

• Reading AVG as total ÷ row count — it is total ÷ non-NULL count.
• Expecting SUM of no rows to be 0 — it is NULL.
• COUNT(column) when you meant COUNT(*) — undercounts by the NULLs.
• An aggregate in WHERE — use HAVING.
• Bare columns beside an aggregate — an error, or worse, silently arbitrary.

Interview question

SELECT AVG(salary) FROM employees returns 80,222 but payroll ÷ headcount is 72,200. Which is right?

Both are arithmetically correct; they answer different questions. AVG excluded the employee with no recorded salary. Which one to report depends on whether that NULL means "zero" or "unknown" — and the answer should be stated, not assumed.

Check yourself

1. When do COUNT(*) and COUNT(col) return the same number?
2. What does SUM(amount) return when no row matches?
3. Why does COUNT(DISTINCT dept_id) return 3 when there are four departments?