1. Aggregate Functions

The Hook

A daily report runs at 3 a.m.: "average customer score." The query:

SELECT AVG(score) FROM customers;

The CEO emails: "Why did our average score drop from 500 to 420 this morning?"

The engineer pulls up the data. Yesterday: 5 customers, scores 350, 900, 750, 500, 0. Average = 500. Today: 6 customers, scores 350, 900, 750, 500, 0, NULL (a new sign-up where the score-calculation job hasn't finished yet). The NULL doesn't poison the average — AVG ignores NULLs. So the average should still be 500…

…except 0 is in the data. AVG doesn't ignore 0. The new sign-up is also not the cause. The real cause: a deleted customer's score is being kept by a soft-delete flag, and that customer's row was just hard-deleted. The average shifted because the underlying data shifted. The query is correct; the data changed.

Or: maybe the query isn't correct. AVG(score) includes Peter's 0. Should it? Is a customer with score 0 actually an active customer, or a placeholder, or an outlier? If you wanted "average score among customers with non-trivial activity," the right query has a FILTER clause, or a WHERE clause, or both:

SELECT AVG(score) FILTER (WHERE score > 0) FROM customers;

This chapter is about aggregate functions — the eight you'll reach for 90% of the time, the modifiers (DISTINCT, FILTER) that change their meaning, and the NULL behaviour that distinguishes "I want the average ignoring missing data" from "I want the average across every customer including zero-score ones." By the end you'll know which aggregate to reach for in each case, when COUNT(*) differs from COUNT(column), and how to write aggregates that correctly handle the messy data your tables actually contain.

Table of contents

1. The eight you'll use most
2. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)
3. SUM, AVG, MIN, MAX
4. STRING_AGG / GROUP_CONCAT
5. Boolean aggregates: BOOL_AND, BOOL_OR
6. The DISTINCT modifier
7. The FILTER clause
8. NULL behaviour
9. Edge cases and pitfalls
10. Production reality
11. Practice ladder
12. Cross-links
13. Final takeaway

The eight aggregates

Aggregates over (100, 200, NULL, 300). COUNT(*) sees 4 rows; COUNT(col) sees 3 non-NULL values. SUM/AVG ignore the NULL — average is 200, not 150.

Plus a couple of less-common but useful ones:

These two are how you ask "do all customers have score > 0" / "does any order exceed $1000" without writing a subquery.

One row out, eight columns of summary statistics over the table. That's the canonical "executive summary" aggregate query.

COUNT variants

COUNT is the most-used aggregate, and its three forms have different meanings. Confusing them is a common bug.

COUNT(*)

Counts every row in the group, regardless of NULL.

* is a placeholder for "the row", not "all columns" — COUNT(*) doesn't actually inspect any column, it just counts row-shaped things. This is why COUNT(*) works on tables that have NULLs, on JOIN results, on subquery outputs.

COUNT(column)

Counts non-NULL values in the named column.

The difference between COUNT(*) and COUNT(country) is how many rows have NULL in country — useful for data-quality queries.

The pattern from GROUP BY and HAVING: after a LEFT JOIN, COUNT(*) always counts at least 1 per outer row (because the row exists), but COUNT(right_side_column) counts 0 when the join didn't match. COUNT(o.order_id) is the right way to count "actual orders per customer" after a LEFT JOIN — COUNT(*) would over-count.

COUNT(DISTINCT column)

Counts distinct non-NULL values.

COUNT(DISTINCT ...) is more expensive than the other forms — to find distinct values, the engine must sort or hash the column. On large tables, COUNT(DISTINCT) over an unindexed column can be slow. There are approximate alternatives (approx_count_distinct, HyperLogLog) for analytics workloads — out of scope for foundations, but useful to know they exist.

SUM, AVG, MIN, MAX

The numeric "Big Four":

All four ignore NULLs. Some subtleties:

• SUM over an empty set returns NULL, not 0. SELECT SUM(sales) FROM orders WHERE order_id < 0 returns NULL. To get 0, wrap it: COALESCE(SUM(sales), 0).
• AVG is integer-divisive in some dialects. Postgres: AVG(int_column) returns numeric (high precision). MySQL/SQLite: also numeric. SQL Server: integer division if the column is INT — surprise. Cast to DECIMAL if portability matters.
• MIN and MAX work on text and dates too (lexicographic for text, chronological for dates) — MAX(country) returns the alphabetically-last country, MIN(order_date) returns the earliest order date.

STRING_AGG

Concatenate values into one string, with a separator:

STRING_AGG shines for "give me a comma-separated list of X per group" — a tags-on-a-blog-post column, all the products in a category, every email recipient for a notification. Postgres also lets you STRING_AGG(... ORDER BY col) to get them sorted, which is essential for deterministic output.

Boolean aggregates

Two of them:

Dialect note: Postgres has true BOOL_AND and BOOL_OR. SQLite and MySQL don't (they treat boolean as integer 0/1), but MIN/MAX over the boolean expression gives the same result: MIN(predicate) is true iff every row's predicate is true, MAX(predicate) is true iff any row's is. Standard-Postgres form:

SELECT BOOL_AND(score > 0) AS all_positive,
       BOOL_OR(score > 800) AS any_above_800
FROM customers;

DISTINCT modifier

AGG(DISTINCT column) aggregates only the distinct non-NULL values:

SUM(DISTINCT sales) collapses ties before summing — if two orders both have sales = 100, only one 100 contributes to the sum. Almost never what you want. SUM(DISTINCT) and AVG(DISTINCT) are dangerous because they silently change the maths in a way that produces plausible-looking results.

COUNT(DISTINCT) is the legitimate use case. The other DISTINCT aggregates are red flags in code review.

FILTER clause

The standard-SQL way to apply a per-aggregate condition. Computes the aggregate over only the rows where the filter is true:

Dialect note: FILTER is standard SQL and supported by PostgreSQL and SQLite (since 3.30, October 2019). MySQL doesn't support it — the workaround is SUM(CASE WHEN condition THEN 1 ELSE 0 END) or AVG(CASE WHEN condition THEN col ELSE NULL END). Both are uglier but portable.

-- The portable rewrite for "count rows with score > 500":
SUM(CASE WHEN score > 500 THEN 1 ELSE 0 END) AS high_score_customers

FILTER is the right tool for "give me different aggregates over different subsets of the same data." Without FILTER, you'd run separate queries and combine — slower and uglier. With FILTER, one query, one pass over the data.

This shape — multiple FILTER clauses in one SELECT — is the classic "dashboard query." A daily executive summary that needs "active users" and "paying users" and "users in Q4" and "users in trial" all in one row is essentially a list of FILTERed aggregates.

NULL behaviour

Aggregate functions handle NULL in a uniform-but-non-obvious way: NULLs are silently excluded from the aggregation (with one exception: COUNT(*)).

SUM(x) is 60. AVG(x) is 20 (60 / 3, not 60 / 4). The NULL is invisible to the aggregate.

This is usually what you want. The trap is that AVG of (10, 20, NULL, 30) is 20, not 15. If you intended the NULLs to count as 0, you have to say so:

SELECT AVG(COALESCE(x, 0)) FROM t;     -- 15

Conversely, if you want to exclude zeroes too, add a filter:

SELECT AVG(x) FILTER (WHERE x > 0) FROM t;

The decision — "do NULLs count?", "do zeroes count?" — is a product decision. The aggregate function does what you tell it; saying nothing means "treat NULL as missing." That default is sensible 80% of the time and a bug 20% of the time. Be explicit in production code.

Edge cases and pitfalls

SUM over an empty set is NULL, not 0

SELECT SUM(sales) FROM orders WHERE 1=0;
-- Returns NULL.

If you want 0, wrap in COALESCE:

SELECT COALESCE(SUM(sales), 0) FROM orders WHERE 1=0;
-- Returns 0.

This bites every dashboard query that aggregates a recent time window — when the window has no rows, you want 0, not NULL. COALESCE is the fix.

COUNT(*) on a JOIN

A common gotcha. COUNT(*) on a LEFT JOIN result counts the joined rows, not the original "left" rows:

Use COUNT(o.order_id) (or any non-NULL right-side column) to count "real" matches. Use COUNT(*) when you genuinely want "the row count of the joined result."

Aggregate of an aggregate isn't allowed

-- ❌ ERROR: nested aggregates not allowed.
SELECT MAX(SUM(sales)) FROM orders GROUP BY customer_id;

To compute "the maximum per-customer total," you need a subquery or CTE:

SELECT MAX(total) FROM (
  SELECT customer_id, SUM(sales) AS total FROM orders GROUP BY customer_id
) t;

Inner query: per-customer totals. Outer query: max of those.

DISTINCT in aggregate vs DISTINCT in SELECT

Two different things:

• SELECT DISTINCT a, b FROM t — keep distinct (a,b) pairs from the result.
• SELECT COUNT(DISTINCT a) FROM t — count distinct values of a.

Both legal, both useful, easy to confuse.

Aggregates and indexes

MIN(col) and MAX(col) on an indexed column are O(1) — the engine just reads the first or last leaf of the B-tree index. SUM, AVG, COUNT always require a full scan (with no index, you must visit every row). This matters when designing schemas for large tables: knowing which aggregates you'll run informs which indexes you need. Full treatment in B-Tree Indexes.

Production reality

Codefolio's hello_events is a counters-and-events table — the natural fit for aggregate queries. Three example queries you'd actually run:

(1) Hourly request rate:

-- Postgres-flavour
SELECT DATE_TRUNC('hour', TO_TIMESTAMP(timestamp_ms / 1000.0)) AS hour,
       COUNT(*) AS requests,
       MAX(visits) - MIN(visits) AS visits_added
FROM hello_events
WHERE timestamp_ms >= EXTRACT(EPOCH FROM NOW() - INTERVAL '24 hours') * 1000
GROUP BY hour
ORDER BY hour;

COUNT(*) is requests-per-hour. MAX(visits) - MIN(visits) is "how much did the visits counter advance during this hour" — a useful proxy for unique successful increments.

(2) Activity dashboard with filters:

-- Postgres-flavour
SELECT
  COUNT(*)                                                  AS total_24h,
  COUNT(*) FILTER (WHERE visits >= 1000)                    AS high_traffic_24h,
  AVG(visits) FILTER (WHERE timestamp_ms >= NOW() - 60000)  AS avg_last_minute
FROM hello_events
WHERE timestamp_ms >= EXTRACT(EPOCH FROM NOW() - INTERVAL '24 hours') * 1000;

Three different aggregates over three different time/threshold subsets, in one pass.

(3) "Top 5 customers" report:

SELECT customer_id,
       COUNT(*)            AS order_count,
       SUM(sales)          AS total_sales,
       AVG(sales)          AS avg_sale,
       MIN(order_date)     AS first_order,
       MAX(order_date)     AS last_order
FROM orders
GROUP BY customer_id
ORDER BY total_sales DESC
LIMIT 5;

Six columns of summary statistics per customer, ranked by total sales. Five rows out. Once you can write this fluently you can write half of analytics SQL.

Practice ladder

1. Count of orders per customer (including customers with zero orders). Hint: LEFT JOIN, then COUNT(o.order_id) (NOT COUNT(*)) per customer.
2. Average score among customers whose score is above 0. Hint: AVG(score) FILTER (WHERE score > 0).
3. The earliest and latest order dates per customer. Hint: MIN(order_date), MAX(order_date), group by customer.
4. A comma-separated list of customer names per country. Hint: GROUP_CONCAT(first_name, ', ') (SQLite/MySQL) or STRING_AGG(first_name, ', ') (Postgres).
5. Predict the result of:
   SELECT AVG(x) FROM (VALUES (10),(20),(NULL),(30)) AS t(x);

   Hint: NULL is excluded from the count and the sum. What's the denominator?
6. Why does this query potentially return NULL instead of 0 for empty time windows?
   SELECT SUM(sales) FROM orders WHERE order_date >= '2099-01-01';

   Hint: SUM over an empty set. The fix?
7. Count of customers per country and count of distinct names per country in one query. Hint: COUNT(*) and COUNT(DISTINCT first_name) in the same SELECT.

Cross-links

• Previous in this module: GROUP BY and HAVING — the mechanics that produce groups for these aggregates to operate on.
• Next in this module: Grouping Sets, ROLLUP, CUBE — multi-dimensional aggregation: subtotals at multiple grouping levels in a single query.
• Forward reference: Window Functions — the same aggregates (SUM, AVG, COUNT, etc.) used as windowed expressions, computing over a sliding window of rows without collapsing the result.
• Forward reference: B-Tree Indexes — MIN/MAX on indexed columns is O(1); SUM/AVG/COUNT always require a scan.

Final Takeaway

Your Turn

Before you move on, check your understanding with the coach — explain the idea, apply it, weigh the trade-offs, then defend your reasoning.