Aggregates — Rolling Up Data

Sometimes you do not want the rows back — you want the story they tell. A manager asking "how many people work here?" does not want fifteen rows on her screen; she wants one number. An aggregate function collapses an entire column down to a single summary value, and which one you pick depends on the question: a count, a total, an average, or the extremes.

The five you will reach for constantly:

Function	What it returns
`COUNT(*)`	The number of rows
`COUNT(column)`	The number of non-NULL values in that column
`SUM(column)`	The total of all values
`AVG(column)`	The mean of the non-NULL values
`MIN(column)` / `MAX(column)`	The smallest and largest values

You can mix several in one query, and rename each result with AS so the single returned row reads like a sentence.

flowchart LR
  C["720000<br/>690000<br/>910000<br/>..."] -->|"AVG"| A["Avg salary"]
  C -->|"COUNT"| N["15 rows"]
  C -->|"MAX"| M["980000"]
  style A fill:#0e7490,color:#fff
  style N fill:#0e7490,color:#fff
  style M fill:#0e7490,color:#fff

Aggregates turn a column of values into a single summary figure.

COUNT(*) versus COUNT(column)

These two look almost identical and behave differently, and the gap between them is the single most common source of wrong summary numbers.

COUNT(*) counts rows — every one of them, no matter what is stored inside. COUNT(column) counts only the rows where that column holds an actual value, skipping anything that is NULL.

Our seed data has exactly one employee, Omar Aziz, with no department. So over employees, COUNT(*) returns 15 while COUNT(department_id) returns 14. That missing row stays invisible until someone asks "why doesn't the headcount add up?".

Run these aggregates against the employees table.

SELECT
  COUNT(*) AS total_employees,
  COUNT(department_id) AS with_department,
  AVG(salary) AS average_salary,
  MIN(salary) AS lowest_salary,
  MAX(salary) AS highest_salary
FROM employees;

Aggregates honour WHERE

An aggregate only ever summarises the rows that reach it. Add a WHERE clause and the function never sees the rows it filtered out — the summary is computed from the survivors alone.

That is how you answer scoped questions. "How many engineers?" is COUNT(*) with WHERE role = 'Engineer'. "The top salary in Sales" is MAX(salary) scoped to that department. The filter always runs first and the aggregate always runs last; you cannot swap that order.

Aggregate over a filtered subset: engineers only.

SELECT COUNT(*) AS engineers, MAX(salary) AS top_engineer_pay
FROM employees
WHERE role = 'Engineer';

Totals, averages, and extremes

SUM, AVG, MIN, and MAX each walk the column once and hand back a single number. SUM(salary) is everything added together; MIN and MAX find the two ends of the range, a quick way to spot outliers without sorting.

MIN and MAX are not just for numbers — they also pick the earliest and latest date, and the alphabetically first and last text value, which is how MAX(hired_on) finds the most recent hire. AVG deserves a closer look: it ignores NULL rows entirely, exactly as COUNT(column) does. It does not treat a missing value as zero. If three rows hold 100, 200, and NULL, the average is 150, not 100, because only the two real numbers were averaged.

flowchart TB
  A["15 employees, one with a NULL department"] --> B["COUNT(*) → 15<br/>every row counts"]
  A --> C["COUNT(department_id) → 14<br/>the NULL row is skipped"]
  A --> D["AVG(salary)<br/>ignores NULLs when averaging"]
  style B fill:#0e7490,color:#fff
  style C fill:#b45309,color:#fff
  style D fill:#0e7490,color:#fff

Same input, three behaviours: COUNT(*) keeps every row, COUNT(column) drops NULLs, AVG ignores them.

Counting distinct values

Sometimes the number you want is not "how many rows" but "how many different values appear." Wrap the column in COUNT(DISTINCT ...) and duplicates collapse before the counting happens.

Our fifteen employees hold only a handful of roles, many of them repeated. COUNT(DISTINCT role) returns the number of unique job titles, not fifteen. The same DISTINCT keyword works inside the other aggregates too — SUM(DISTINCT column) adds each value just once — though in practice you will almost always pair DISTINCT with COUNT.

What an empty result returns

Every aggregate answers one question beginners forget to ask: what comes back when no rows match? COUNT(*) and COUNT(column) return 0 — they are counts, and "none" is a number. The others — SUM, AVG, MIN, MAX — return NULL, because there is no total or average of nothing.

That matters the moment your WHERE matches nothing: a 0 means "I counted zero rows," while a NULL means "there was nothing to average." Telling them apart is exactly what COUNT versus AVG is for.

One query, one row

Notice that every aggregate query so far has returned a single row, even though fifteen employees sat behind it. Without a GROUP BY clause, the database has no way to print a plain column next to a single average — SELECT name, AVG(salary) is rejected, because which of the fifteen names would it choose?

That is the wall you are about to break through. The next lesson splits these summaries apart by group, so each department can carry its own row of totals.

Exercise

Return a single row with the total number of employees as total_employees and the average salary as average_salary.

SELECT
  -- your aggregates here
FROM employees;

SELECT COUNT(*) AS total_employees, AVG(salary) AS average_salary FROM employees;

Exercise

Compare two counts from the employees table: the total number of employees (total_employees), and the number who belong to a department (assigned_employees). Return both in one row.

SELECT
  -- two different COUNT expressions
FROM employees;

SELECT COUNT(*) AS total_employees, COUNT(department_id) AS assigned_employees FROM employees;

Exercise

This query was meant to report how many different roles exist in the company, but it returns 15 — the headcount, not the count of unique job titles. Repair the aggregate so it collapses duplicates first.

SELECT COUNT(role) AS distinct_roles
FROM employees
;

SELECT COUNT(DISTINCT role) AS distinct_roles FROM employees;

Exercise

Return a single row with one column, pay_range: the gap between the top and bottom salaries, computed as the highest salary minus the lowest. (Combine two aggregates in one expression — this one is not copyable from the text above.)

SELECT
  -- combine two aggregates into one expression
FROM employees
;

SELECT MAX(salary) - MIN(salary) AS pay_range FROM employees;

Recap

An aggregate collapses a column to one value: COUNT, SUM, AVG, MIN, MAX.
COUNT(*) counts rows; COUNT(column) skips NULLs, so they usually disagree.
AVG, SUM, MIN, and MAX all ignore NULL instead of treating it as zero.
WHERE runs first; the aggregate runs last, over whatever rows survived.
With no GROUP BY, an aggregate query always returns exactly one row.

Next you'll group rows into buckets so each department earns its own summary.

Aggregates — Rolling Up Data

COUNT(*) versus COUNT(column)

Aggregates honour WHERE

Totals, averages, and extremes

Counting distinct values

What an empty result returns

One query, one row

Recap

Checkpoint quiz

Go deeper — technical resources