Aggregates in SQL

Aggregate functions collapse many rows into a single value. Instead of getting a list of every row, you get one number: a total, an average, a count.

These are the queries that answer business questions. "How many users do we have?" "What's the total revenue?" "Which continent has the highest GDP?"

Here's a walkthrough using a world database with country data.

Total population of the world

SELECT SUM(population)
FROM world

SUM adds up every value in the population column. One row back. One number.

List each continent once

SELECT DISTINCT continent
FROM world

DISTINCT removes duplicates. Without it, you'd get "Europe" repeated for every European country.

GDP of Africa

SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa'

The WHERE clause filters rows before the aggregate runs. Only African countries contribute to this sum.

Count countries by continent

SELECT continent, COUNT(name)
FROM world
GROUP BY continent

GROUP BY splits the data into groups. COUNT runs separately on each group. You get one row per continent with the number of countries in each.

Continents with large populations

SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population) > 500000000

HAVING filters after grouping. WHERE filters individual rows. HAVING filters groups. This returns only continents where the total population exceeds 500 million.

The pattern

Most aggregate queries follow the same shape:

1. WHERE filters rows
2. GROUP BY creates groups
3. Aggregate functions (SUM, COUNT, AVG, MIN, MAX) compute per group
4. HAVING filters groups

Get this pattern into muscle memory. It covers 90% of reporting queries you'll ever write.