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).
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 |
Run on all six accounts:
| accounts | total_myr | avg_myr |
|---|---|---|
| 6 | 24550.00 | 4091.67 |
One summary row for the bank
COUNT(*) vs. COUNT(column) vs. COUNT(DISTINCT column)
These three look similar and behave very differently:
| all_rows | rows_with_a_code | distinct_codes | distinct_types |
|---|---|---|---|
| 6 | 3 | 2 | 2 |
COUNT(*) counts every row, no exceptions. COUNT(referral_code) counts only the rows where that column isn't NULL, only 3 of the 6 accounts have a code. COUNT(DISTINCT referral_code) counts unique non-NULL values (REF-A and REF-B, just 2), 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.
Three different questions, three different COUNTs
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.