Aggregation

Summarizing many rows into one

Aggregate functions collapse a column of values into a single result — a count, a sum, an average. Pair them with GROUP BY and you can summarize data per category.

The core aggregate functions

• COUNT(*) — number of rows
• COUNT(column) — number of non-NULL values in that column
• SUM(column) — total of numeric values
• AVG(column) — arithmetic mean (ignores NULLs)
• MIN(column) / MAX(column) — smallest / largest value

GROUP BY

GROUP BY splits rows into buckets, then runs the aggregate once per bucket:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Every column in the SELECT that isn't aggregated must appear in GROUP BY.

Think of GROUP BY as "collapse these rows by this key, then give me one summary row per key."

HAVING vs WHERE

This is the most common source of confusion:

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'      -- filter ROWS first
GROUP BY department
HAVING COUNT(*) > 5;         -- filter GROUPS after

• WHERE filters individual rows before grouping.
• HAVING filters groups after aggregation. It can use aggregate functions; WHERE cannot.

COUNT(*) vs COUNT(column)

COUNT(*) counts every row. COUNT(column) skips rows where that column is NULL — useful for finding how many records actually have data in a field.