Aggregate Functions: COUNT, SUM, AVG, MIN, MAX (and the three COUNTs)

Aggregates summarize a column to one value

An aggregate function takes a whole column of values and returns a single summary value. The five you must know:

- COUNT(...) — how many. - SUM(col) — total of a numeric column. - AVG(col) — arithmetic mean. - MIN(col) / MAX(col) — smallest / largest.

Used with no GROUP BY, an aggregate produces a scalar aggregate — exactly one row:

SELECT SUM(budget) AS total_budget, MAX(budget) AS biggest FROM projects;

Key rule: you cannot put a bare column *and* an aggregate in the same SELECT without a GROUP BY — SELECT name, AVG(salary) FROM employees is an error, because name has one value per row while AVG(salary) has one value for the whole table. (We fix that with GROUP BY in the next lesson.)

The three COUNTs — the exam’s favorite distinction

All three count, but they count *different things*, and nulls and duplicates are what separate them:

- COUNT(*) counts every row the query selects, including rows where some column is NULL. - COUNT(col) counts only rows where col is non-null — it *skips* nulls. - COUNT(DISTINCT col) counts the unique non-null values of col.

They coincide only when the column is both non-null and duplicate-free.

Worked example

In the employees table, 11 of 12 rows have a manager_id (the CEO-equivalent has NULL):

SELECT COUNT(*)          AS all_rows,      -- 12  (counts every employee)
       COUNT(manager_id) AS has_manager   -- 11  (skips the NULL manager_id)
FROM employees;

COUNT(*) = 12 because it counts rows; COUNT(manager_id) = 11 because one row’s manager_id is null. Now the assignment table records who works on projects, with employees appearing on multiple projects:

SELECT COUNT(emp_id)          AS rows_in_assignments,   -- 13 line items
       COUNT(DISTINCT emp_id) AS people_assigned        --  9 distinct people
FROM assignments;

COUNT(DISTINCT emp_id) collapses the repeats to the 9 different employees actually assigned — which is the number you want for “how many people work on projects,” not the 13 raw rows.