Chapter 14 · intermediate · 7 min
Aggregate functions: SUM, AVG, COUNT, MIN, MAX
So far every query returned rows straight from the table. Aggregation is different: it squeezes many rows into one number. "How many accounts?", "What's the average balance?", all aggregation. We'll start with the five core functions, applied to the whole table at once; grouping comes next.
We'll summarise bank accounts.
The dataset
An accounts table: each account has an owner, a type (savings/current), a branch, a balance, and an optional referral code (some are NULL).
Schema
| id | INT |
| owner | TEXT |
| acct_type | TEXT |
| branch | TEXT |
| balance | NUMERIC |
| referral_code | TEXT |
Example data
Aggregate the whole table
With no GROUP BY (the next chapter), an aggregate function folds the entire table into a single row. The core five:
| Function | Returns |
|---|---|
COUNT(*) | number of rows |
SUM(col) | total |
AVG(col) | average |
MIN(col) | smallest value |
MAX(col) | largest value |
input rows
| owner | balance |
|---|---|
| Aisyah | 5200.00 |
| Bala | 1300.00 |
| Chong | 8800.00 |
| Devi | 450.00 |
| Farid | 2100.00 |
| Gina | 6700.00 |
group rows
| accounts | total_myr | avg_myr |
|---|---|---|
| 6 | 24550.00 | 4091.67 |
6 input rows collapse into 1 group — a column not in GROUP BY has no single value left to show per group
Before you run it — there are 6 accounts. What total and average balance do you expect, roughly?
COUNT(*) vs. COUNT(column) vs. COUNT(DISTINCT column)
These three look similar and behave very differently. COUNT(*) counts every row, no exceptions. COUNT(referral_code) counts only the rows where that column isn't NULL. COUNT(DISTINCT referral_code) counts unique non-NULL values, useful for "how many different X" questions. Mixing these up is one of the most common aggregation mistakes in real interviews; always ask which of the three you actually need before writing COUNT.
Before you run it — only 3 of 6 accounts have a referral_code, and the non-NULL codes are REF-A, REF-A, REF-B. What do you expect for all_rows, rows_with_a_code, and distinct_codes?
If you've used Excel or Google Sheets
These map almost directly: SUM, AVERAGE (SQL's AVG), COUNT, MIN, and MAX all have identical or near-identical names in spreadsheet formulas. The one real gap in intuition is COUNT itself: Excel's COUNT() only counts numeric cells (closer to SQL's COUNT(column) on a numeric column), while COUNTA() counts any non-blank cell. SQL's plain COUNT(*) has no exact spreadsheet equivalent; it's closer to a plain "number of rows" than either Excel function.
Ready to practice? Grab's ad click conversion rate question below leans directly on COUNT(DISTINCT ...).
These five functions are the vocabulary; the next chapter, GROUP BY, is what lets you run them per category instead of over the whole table at once.