Lesson Beginner

COUNT / SUM / AVG / MIN / MAX

Aggregate functions collapse many rows into a single number.
"How many orders?" "What's the total revenue?" "What's the average basket size?" Every one of those questions is an aggregate.

The Five You Will Use Every Day #

Function	Returns
`COUNT(*)`	Number of rows, including rows with NULLs
`COUNT(col)`	Number of rows where `col` is not NULL
`SUM(col)`	Sum of non-NULL values
`AVG(col)`	Average of non-NULL values
`MIN(col)` / `MAX(col)`	Smallest / largest non-NULL value

With no GROUP BY (which is the next lesson), an aggregate collapses the entire table into a single output row.

sql

SELECT
  COUNT(*) AS order_count,
  ROUND(SUM(total), 2) AS total_revenue,
  ROUND(AVG(total), 2) AS avg_order
FROM orders;

COUNT(*) vs COUNT(column) #

These are not the same, and the difference is where most aggregation bugs start:

COUNT(*) counts rows. NULLs count.
COUNT(column) counts non-NULL values in that column.
COUNT(DISTINCT column) counts unique non-NULL values.

If a column has 1,000 rows and 200 NULLs, COUNT(*) returns 1,000 and COUNT(column) returns 800.

Warning

SUM, AVG, MIN, and MAX silently ignore NULL values.
That is usually what you want, but it changes denominators. AVG(discount) over 1,000 rows where 400 discounts are NULL averages over 600 rows, not 1,000. If that matters for your metric, coalesce NULLs to zero first: AVG(COALESCE(discount, 0)).

The Aggregation Pipeline #

Before the next two lessons, keep this mental model handy:

WHERE filters rows → GROUP BY splits survivors into buckets → aggregates compute per bucket → HAVING filters buckets.

This lesson covers the "aggregates compute" step with no grouping. The rest of the section adds the pieces around it.

Try It #

A single query returning six aggregate metrics for the whole orders table.

Loading SQL editor...

Practice #

Return a single row with three columns for paid orders only:

paid_count: the number of paid orders,
paid_revenue: the sum of total, rounded to 2 decimals,
avg_paid_order: the average total, rounded to 2 decimals.

Loading SQL editor...

Mistakes to Watch For #

Using COUNT(column) when you meant COUNT(*): the NULL rows silently disappear.
Forgetting that AVG ignores NULLs and changes the denominator.
Mixing an aggregate with a non-aggregate column in SELECT without a GROUP BY. SQL will reject the query. Covered in the next lesson.
Assuming SUM over an empty result set returns 0. It returns NULL. Wrap in COALESCE(SUM(col), 0) when that matters.

Knowledge check #

3 questions

0 / 3 answered

COUNT(*) and COUNT(column) differ how?
AVG(amount) over the values (100, NULL, 50, NULL, 200) returns:
Over an empty table, SUM(amount) returns:

Next Step #

Continue to GROUP BY to compute these same aggregates per bucket instead of over the whole table.