So far every query returned rows that exist in the table. But stakeholders rarely want rows — they want numbers. How many orders? What's the average basket? Total revenue this month? Aggregate functions are how a million rows become one honest answer.
What you'll learn
What
An aggregate function consumes a set of rows and returns a single value: COUNT (how many), SUM (total), AVG (mean), MIN and MAX (extremes). Used without grouping, they collapse the whole filtered table into exactly one row.
Why
Metrics are aggregations. Revenue, conversion rate, average order value, active-user counts — every KPI on every dashboard is an aggregate over some filtered slice of data. Getting them right (especially around NULLs and duplicates) is the difference between a trusted analyst and a corrected one.
Where it's used
Every dashboard tile, every A/B-test readout, every data-quality check ('how many rows loaded today?'), and the profiling queries you run the moment you meet a new table.
Where this runs in production
Each store's home screen — total sales, order count, average order value — is a handful of SUM/COUNT/AVG queries over that shop's orders.
The DAU metric is COUNT(DISTINCT user_id) over a day's lesson-completion events — DISTINCT because one learner can finish many lessons.
Pricing teams track AVG(items_per_order) and MAX delivery distances per region to tune batching and delivery fees.
Think of an aggregate query as a funnel: WHERE pours in the rows you care about, the aggregate function is the narrow neck, and out drips a single drop — one number. The rows themselves never leave the database; only the summary does. That's why aggregating in SQL beats downloading a million rows to average them in a spreadsheet.
COUNT(*) counts rows — every row, NULLs and all. COUNT(column) counts rows where that column is NOT NULL. COUNT(DISTINCT column) counts unique non-NULL values. Three different questions, three different numbers: 1,000 orders, 940 with a known coupon code, 12 distinct coupon codes.
SELECT COUNT(*) AS total_rows, COUNT(discount_pct) AS with_discount_value, COUNT(DISTINCT customer_id) AS unique_customers FROM orders;
You can compute several aggregates in one pass over the table — one query, one scan, three metrics. The difference total_rows − with_discount_value is the NULL count, the standard profiling trick from the WHERE lesson, now in aggregate form.
SUM, AVG, MIN, and MAX ignore NULL values entirely. AVG(rating) over ratings 4, 5, and NULL is 4.5 — the mean of two values, not three. If the business question treats missing as zero, you must say so explicitly: AVG(COALESCE(rating, 0)) gives 3.0. Neither is 'the right answer' universally; the point is that YOU choose, consciously.
When handed an unknown table, run one aggregate query per interesting column: COUNT(*), COUNT(col), COUNT(DISTINCT col), MIN(col), MAX(col). Five numbers tell you size, missingness, cardinality (how many distinct values a column has), and range — including surprises like MAX(order_date) in the future or MIN(amount) negative. Analysts at every serious shop run this before trusting any table.
SELECT ROUND(AVG(amount), 2) AS avg_order_value, MIN(created_at) AS first_order, MAX(created_at) AS latest_order FROM orders WHERE status = 'shipped';
Aggregates compose with everything you know: WHERE filters the input set first, and scalar functions like ROUND dress the output. MIN/MAX on timestamps give you a table's time coverage in one glance.
The same input — amounts 100, 200, NULL, 300 — flows into each function. Click each node to see exactly how it handles the NULL.
Select a type to see its full definition, operations, and data science usage.
How many orders does the shop have in total?
COUNT(*) counts every row. The result is one row, one column. Aliasing the aggregate (AS total_orders) is near-mandatory in practice — unaliased aggregates surface as unhelpful headers like count or ?column?.
total_orders
--------------
1000
(1 row)Your task
Profile the orders table (order_id, customer_id, amount, coupon_code, status). In ONE query over non-cancelled orders, compute: total order count (order_count), total revenue (total_revenue), average order value rounded to 2 decimals (avg_value), and number of distinct customers (unique_customers). Fill in the blanks.
order_count | total_revenue | avg_value | unique_customers
-------------+---------------+-----------+------------------
3 | 600.00 | 200.00 | 2
(1 row)Write your solution in the editor on the right, then hit Run.
The email column is NULL in 200 of 1,000 rows. What does COUNT(email) return?
Which aggregate works on text columns?
The ratings column contains 4, NULL, 2. What does AVG(ratings) return?
Why is SELECT customer_id, COUNT(*) FROM orders; rejected by the database?
What is happening, and what is the minimal fix?
The tickets table has satisfaction_rating (1–5, NULL when the survey was skipped). Write one query returning: the average rating among respondents rounded to 2 decimals (avg_rating), and the response rate as respondents divided by all tickets rounded to 2 decimals (response_rate). Avoid integer division.
Explain the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column). When does each give a different number?
COUNT(*) counts rows in the input set — NULLs are irrelevant because it never looks at a value. COUNT(column) counts rows where that column is non-NULL, so it diverges from COUNT(*) exactly when the column has missing values; the difference is the NULL count, which makes the pair a one-line missingness profile. COUNT(DISTINCT column) collapses duplicate values first and also ignores NULLs, so it measures cardinality — 380 coupon-using orders might involve only 9 distinct codes. A concrete example: on an orders table with 1,000 rows, 60 missing coupon codes, and heavy code reuse, the three counts might read 1,000 / 940 / 9 — three different business questions answered by what looks like the same function. In interviews I'd add that COUNT(DISTINCT) can be significantly more expensive at scale because it must track the set of values seen.
How do aggregate functions treat NULLs, and how can that mislead a metric?
Every standard aggregate except COUNT(*) skips NULLs: SUM adds only real values, MIN/MAX consider only real values, and AVG divides by the count of non-NULL inputs — not the row count. The classic trap is a satisfaction or rating average: AVG over (5, 4, NULL, NULL, 3) is 4.0, the respondent mean, but reporting it as 'customer satisfaction' hides that 40% never answered. Whether missing should mean 'excluded' or 'zero' is a business decision — AVG(col) versus AVG(COALESCE(col, 0)) — and the honest report usually shows the respondent mean alongside the response rate. A second trap is the empty set: SUM/AVG/MIN/MAX return NULL over zero rows, so dashboard queries should wrap sums in COALESCE(…, 0) to avoid blank tiles and NULL-propagating downstream arithmetic.
You're given a brand-new table nobody documented. What aggregate queries do you run first, and what is each one checking?
I run a profiling battery before trusting anything. COUNT(*) establishes scale. COUNT(DISTINCT primary-key-candidate) against COUNT(*) checks uniqueness — a mismatch means duplicates, and every downstream SUM would double-count. COUNT(*) − COUNT(col) per important column measures missingness. MIN and MAX on numeric columns expose range problems: negative amounts (refunds mixed in), absurd maxima (cents-vs-dollars confusion, test rows). MIN and MAX on the date column verify time coverage against what the data was claimed to contain — a common way to catch truncated extracts. Finally COUNT(DISTINCT category-like columns) reveals cardinality, which tells me whether a column is a clean enum or free text. Ten minutes of aggregates routinely finds defects that would otherwise surface as a wrong executive number weeks later.
Common Mistakes to Avoid
1) Assuming AVG divides by the row count — it divides by the non-NULL count; decide explicitly whether NULL means 'exclude' or 'zero'. 2) Forgetting SUM/AVG/MIN/MAX return NULL on an empty set — wrap dashboard sums in COALESCE(…, 0). 3) Mixing a bare column with an aggregate (SELECT customer_id, COUNT(*)) without GROUP BY — the database rightly refuses. 4) Using COUNT(*) when the question is about unique entities — 1,000 orders is not 1,000 customers; reach for COUNT(DISTINCT customer_id). 5) Integer division in manual ratios — 3/5 is 0 in integer arithmetic; cast one side to numeric/decimal first.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: why does AVG ignore NULLs, and when is that wrong?' • 'Quiz me: small value lists, I predict COUNT(*)/COUNT(col)/COUNT(DISTINCT col).' • 'Show me a real dashboard bug caused by SUM returning NULL on an empty day.' • 'Walk me through profiling an unknown table with five aggregates.' • 'Interview mode: grill me on the three COUNTs and grade my answer.'
Glossary
Aggregate function — a function that consumes a set of rows and returns one value. COUNT(*) — number of rows in the input set. COUNT(column) — number of non-NULL values in a column. COUNT(DISTINCT column) — number of unique non-NULL values. SUM — total of non-NULL numeric values. AVG — arithmetic mean of non-NULL values. MIN / MAX — smallest / largest non-NULL value; also valid for text and dates. Cardinality — the number of distinct values in a column. COALESCE — substitutes a default for NULL; used to make missing mean zero, or to guard empty-set sums. Empty-set behavior — COUNT returns 0; all other aggregates return NULL. Profiling battery — the standard first-contact query set: counts, distincts, null gaps, extremes. Integer division — division of two integers that truncates the fraction; avoided by casting to numeric.
Recommended Resources
• Docs: PostgreSQL manual — 'Aggregate Functions' for the full list beyond the core five (STRING_AGG, ARRAY_AGG, statistical aggregates). • Read: 'Aggregate Functions' chapter of Mode's SQL tutorial for business-flavored practice sets. • Practice: profile any real table you have with the five-aggregate battery, then write down one surprise it revealed — there is always one. • Next in DSM: one number for the whole table is rarely enough — GROUP BY & HAVING turns these same functions into per-category breakdowns.
Recap
✓ Aggregates collapse a set of rows into one value: COUNT, SUM, AVG, MIN, MAX — one output row when there's no grouping. ✓ COUNT(*) counts rows; COUNT(col) counts non-NULL values; COUNT(DISTINCT col) counts unique values — three different questions. ✓ SUM, AVG, MIN, MAX skip NULLs; AVG divides by the non-NULL count, so decide consciously whether missing means excluded or zero. ✓ Over an empty set, COUNT returns 0 but the others return NULL — COALESCE(SUM(…), 0) keeps dashboards honest. ✓ Bare columns can't mix with aggregates without grouping, and aggregates can't appear in WHERE. ✓ The profiling battery (counts, distincts, null gaps, extremes) is your first move on any unfamiliar table. Next up: GROUP BY & HAVING. One number for the whole table answers 'how much' — next you'll split the table into groups and get that number per country, per month, per product, all in a single query.
Run your code to see the output here.