SQL · Lesson 4 of 8

Aggregates & GROUP BY

COUNT, SUM, AVG, MIN, MAX, grouping rows and filtering groups with HAVING.

Aggregate functions turn many rows into one value: COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x).

GROUP BY splits rows into groups and calculates the aggregate for each — for example the number of jobs per city. Every selected column must either be in the GROUP BY or inside an aggregate.

HAVING filters the groups: HAVING COUNT(*) >= 2.

Try it yourself

Code editor

Press Run to see the results.

Runs on a small fictional sample database (companies, jobs) that is rebuilt on every run, so you can't break anything.

Key points

Aggregates summarise many rows.
GROUP BY makes one result row per group.
HAVING filters groups; WHERE filters rows.

Quick check

Which clause filters groups after GROUP BY?

WHERE HAVING LIMIT FILTER

Practice task

Count how many jobs each company_id has posted, including the NULL group.

Interview corner

COUNT(*) vs COUNT(column)?

COUNT(*) counts all rows. COUNT(column) counts only rows where that column is not NULL.

What is the logical order a SELECT runs in?

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That's why a SELECT alias can't be used in WHERE.