Aggregate Functions

Level 2: Level 2: Aggregation & Joins (Combining Source Data)easy20 minCOUNTSUMAVGMINMAXCOUNT(DISTINCT)NULL handling in aggregates

Collapse many rows into a single measure: the atom of every metric.

Why aggregates are the first thing you run

Every dashboard number you have ever seen (total revenue, active users, average order value) is an aggregate: a function that eats many rows and emits one value. The moment a table lands in your warehouse, before you build anything on top of it, you run a handful of aggregates as smoke tests. Is COUNT(*) the row count you expected? Is SUM(total_cents) in the right ballpark? Is MAX(total_cents) impossibly large (a data-entry typo with extra zeros, or a test row that leaked into prod)? Aggregates are how you catch a broken load in ten seconds instead of after it has corrupted a report.

The mental model: many rows collapse to one

An aggregate takes a set of rows and returns a single scalar. With no GROUP BY, the entire table is one group, so a SELECT of only aggregates always returns exactly one row, no matter how many rows went in. (Next module: GROUP BY splits the table into groups and returns one row per group. Same functions, finer grain.)

The five workhorses:

Function	Returns
`COUNT(*)`	number of rows, including all-`NULL` rows
`COUNT(col)`	number of non-NULL values in `col`
`COUNT(DISTINCT col)`	number of distinct non-`NULL` values
`SUM(expr)` / `AVG(expr)`	total / mean of non-`NULL` values
`MIN(col)` / `MAX(col)`	smallest / largest value

SUM and AVG accept any expression, not just a bare column, so SUM(quantity * unit_price_cents) totals a per-row calculation in one pass.

Worked example

The demo below runs this health check against six seed orders. It returns one row: row_count = 6, distinct_customers = 3 (customers 1, 2, and 3, with the two guest NULLs skipped), total_revenue_cents = 18900, avg_order_cents = 4725.0, and largest_order_cents = 9900.

The NULL rule that trips everyone

Table

AVG(total_cents) sums the four non-NULL totals (18900) and divides by 4, not 6: 4725, never 3150. The two NULL totals (orders 104 and 105) are skipped, not counted as zero.
order_id	customer_id	total_cents	status
100	1	2500	paid
101	1	5000	paid
102	2	9900	shipped
103	NULL	1500	paid
104	3	NULL	abandoned
105	NULL	NULL	abandoned

AVG(total_cents) sums the four non-NULL totals (18900) and divides by 4, not 6: 4725, never 3150. The two NULL totals (orders 104 and 105) are skipped, not counted as zero.

AVG ignores NULLs entirely. It does not treat them as zero. Two of the six orders have a NULL total, so AVG(total_cents) divides by 4 (the non-NULL count), not 6: 18900 / 4 = 4725, never 18900 / 6 = 3150. Put precisely, AVG(col) equals SUM(col) / COUNT(col), never SUM(col) / COUNT(*).

If the business wants NULLs counted as zero, push the default inside the function: AVG(COALESCE(total_cents, 0)) returns 3150.0. The two queries give different answers, and knowing which one the business meant is your job. Likewise, COUNT(*) and COUNT(col) diverge the instant col has a NULL, so when someone asks "how many orders have a customer?", answer with COUNT(customer_id), not COUNT(*). Say what you count.

Interview nuance: on an empty set (or a column that is entirely NULL), SUM, AVG, MIN, and MAX return NULL, but COUNT returns 0. This is why production pipelines wrap totals as COALESCE(SUM(amount), 0): an all-refunded day should report 0 revenue, not a NULL that silently poisons the next join or arithmetic step downstream.

Sample data for this example

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER,       -- NULL for guest checkout
  total_cents INTEGER,       -- NULL for abandoned
  status      TEXT
);
INSERT INTO orders VALUES
  (100, 1,    2500, 'paid'),
  (101, 1,    5000, 'paid'),
  (102, 2,    9900, 'shipped'),
  (103, NULL, 1500, 'paid'),      -- guest
  (104, 3,    NULL, 'abandoned'), -- no total
  (105, NULL, NULL, 'abandoned'); -- guest + no total

Worked example (SQL)

SELECT
  COUNT(*)                    AS row_count,
  COUNT(DISTINCT customer_id) AS distinct_customers,
  SUM(total_cents)            AS total_revenue_cents,
  AVG(total_cents)            AS avg_order_cents,
  MAX(total_cents)            AS largest_order_cents
FROM orders;

Apply

Your turn

The task this lesson builds to.

Write a query against order_items (one row per line item) that returns a single summary row with two columns:

total_revenue: the sum of quantity * unit_price_cents across every line
order_count: the count of distinct order_id values

Alias the output columns exactly total_revenue and order_count.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, plus 3 bonus drills.

Source-health scorecard. You own the nightly orders load. Before anything downstream reads the table, you run a one-row scorecard to catch a broken load in seconds. From orders, return these four columns, in order:

total_rows: every row in the table
distinct_customers: count of distinct non-NULL customer_id values
total_revenue: sum of total_cents, treating NULL totals as 0
avg_order_value: average of total_cents over the rows where it is not NULL (plain AVG, which already skips NULLs)

Note that some rows have a NULL customer_id (guest checkouts) and some have a NULL total_cents (abandoned). Your scorecard must handle both correctly.

4 hints and 1 automated check are waiting in the workspace.