Aggregates and GROUP BY

7 pages · ~5 min✓ Reviewed

Builds on Queries and Joins. Next up: Subqueries and CTEs.

SQLPage 1

Aggregates and the Grouping Mental Model

1. Aggregates and groups

An aggregate collapses many rows into one value
COUNT, SUM, AVG, MIN, MAX
No GROUP BY: the whole table is one group, exactly one row
GROUP BY splits rows into buckets: one output row per bucket

sql

SELECT dept, COUNT(*)
FROM emp GROUP BY dept;

dept	COUNT(*)
Sales	3
HR	2

one row per dept

2. Ungrouped columns

Engine	Column not in GROUP BY
SQL Server	error (strict)
PostgreSQL, MySQL 5.7+	ok if functionally dependent on the key
SQLite	bare column accepted

Portable rule

Every non-aggregated SELECT column goes in GROUP BY, or inside an aggregate. Not an error in every engine, but safe in all.

3. Logical order

FROM
WHERErows
GROUP BY
HAVINGgroups
SELECT
ORDER BY

SQLPage 2

Core Aggregate Functions

1. What each one does

COUNT(*) counts rows; COUNT(col) counts non-NULL values
SUM and AVG ignore NULLs
AVG = SUM / COUNT(col), not the row count
MIN and MAX work on numbers, strings and dates, and ignore NULLs

Function	NULL values	Zero rows
`COUNT(*)`	counted	0
`COUNT(col)`	skipped	0
`SUM`, `AVG`	skipped	NULL
`MIN`, `MAX`	skipped	NULL

2. Gotchas

Over zero rows only COUNT returns 0; SUM, AVG, MAX return NULL

AVG skips NULLs

Values 10, 20, NULL give AVG = 15 (30 / 2), not 10 (30 / 3).

3. Example

One output row per dept: row count, average and max salary

sql

SELECT dept, COUNT(*), AVG(salary), MAX(salary)
FROM emp
GROUP BY dept;

dept	COUNT(*)	AVG(salary)	MAX(salary)
IT	3	60000	80000
HR	2	50000	55000

SQLPage 3

WHERE vs HAVING

1. Row filter vs group filter

FROM
WHEREfilters rows
GROUP BYmakes groups
HAVINGfilters groups
SELECT

	WHERE	HAVING
Filters	rows	groups
Runs	before GROUP BY	after aggregation
Aggregates	not allowed	allowed

2. Example

Keep active rows first, then keep depts with more than 5 of them

sql

SELECT dept, COUNT(*) FROM emp
WHERE active = 1
GROUP BY dept
HAVING COUNT(*) > 5;

3. Rules

Non-aggregate condition goes in WHERE: shrinks data before grouping, faster
WHERE COUNT(*) > 5 is an error: no groups exist yet
HAVING without GROUP BY treats the whole table as one group

Remember

Row condition? WHERE. Aggregate condition? HAVING.

SQLPage 4

DISTINCT and Counting with NULLs

1. DISTINCT

SELECT DISTINCT a, b dedupes the whole row, not just a
COUNT(DISTINCT col) counts unique non-NULL values

2. Three counts, three numbers

On 5, 5, NULL, 7	Counts	Result
`COUNT(*)`	all rows	4
`COUNT(col)`	non-NULL	3
`COUNT(DISTINCT col)`	unique non-NULL	2

3. NULL rules

NULL = NULL is not true: use IS NULL
DISTINCT and GROUP BY treat all NULLs as one group
Count NULLs with a CASE sum or COUNT(*) - COUNT(col)

sql

SELECT SUM(CASE WHEN col IS NULL
  THEN 1 ELSE 0 END) AS n1,
  COUNT(*) - COUNT(col) AS n2
FROM t;

4. Interview Point

Why do the three counts differ? COUNT(col) skips NULLs, DISTINCT drops repeats
Is NULL = NULL true? No; use IS NULL
What does DISTINCT a, b dedupe? The (a, b) pair

SQLPage 5

CASE Expressions and Conditional Aggregation

1. CASE Basics

CASE WHEN c1 THEN x WHEN c2 THEN y ELSE z END
First matching branch wins; later ones are skipped
No ELSE: unmatched rows return NULL

2. Conditional Aggregation

Conditional count: SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)
Pivot-style: one aggregate per output column
Without ELSE, non-matches are NULL and SUM skips them

sql

SELECT cust,
  SUM(CASE WHEN year = 2024 THEN amt END) AS y2024,
  SUM(CASE WHEN year = 2025 THEN amt END) AS y2025
FROM sales GROUP BY cust;

one row per cust, one column per year

3. Where CASE Fits

Place	Use
`SELECT`	Label or bucket a value
`ORDER BY`	Custom sort order
`GROUP BY`	Group by a computed bucket
Inside aggregate	Conditional `SUM` / `COUNT`

Empty pivot cell

A cell with no matching rows is NULL, not 0. Use ELSE 0 or COALESCE for zero.

Why CASE inside SUM? One pass gives several filtered totals
Missing ELSE? Unmatched rows become NULL

SQLPage 6

Aggregates Cheat Sheet

1. Counting and NULLs

Call	Result
`COUNT(*)`	all rows
`COUNT(col)`	non-NULL values
`COUNT(DISTINCT col)`	unique non-NULL values
`SUM` `AVG` `MIN` `MAX`	skip NULLs
Empty input	NULL, except `COUNT` gives 0

2. WHERE vs HAVING

FROM
WHEREfilters rows
GROUP BYbuckets
HAVINGfilters groups
SELECT

Select list columns: in GROUP BY or inside an aggregate (portable rule)
Aggregates are fine in HAVING, never in WHERE
Plain conditions go in WHERE: less data to group

3. Conditional Aggregate

SUM(CASE WHEN cond THEN 1 ELSE 0 END) counts the rows matching cond

sql

SELECT dept, COUNT(*) AS n,
  SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) AS act
FROM emp GROUP BY dept HAVING COUNT(*) > 5;

4. Interview Point

**Why does AVG differ from SUM/COUNT(*)?** AVG divides by COUNT(col); NULL rows leave the denominator.
When HAVING over WHERE? When the test uses an aggregate; row-level tests go in WHERE.

SQLPage 7

Check yourself

A dept has salaries 10, 20 and NULL. What do AVG(salary) and SUM(salary)/COUNT(*) return?

AVG(salary) is 15: 30 / 2 non-NULL values
SUM(salary)/COUNT(*) is 10: 30 / 3 rows
AVG divides by COUNT(col), not the row count

Spot the bug: SELECT dept, COUNT(*) FROM emp WHERE COUNT(*) > 5 GROUP BY dept;

Aggregates are not allowed in WHERE, which runs before grouping
Use HAVING COUNT(*) > 5 after GROUP BY

Column x holds 1, 1, NULL. What do COUNT(*), COUNT(x) and COUNT(DISTINCT x) return?

3, 2, 1
Rows, then non-NULL values, then unique non-NULL values