Counting and totaling

COUNT, SUM, AVG, MIN and MAX turn many rows into one number.

An aggregate function takes many rows and returns one value. Without GROUP BY (next lesson), you get a single row for the whole table.

SELECT COUNT(*)      AS deals,
       SUM(amount)   AS total_value,
       AVG(amount)   AS average_deal,
       MIN(amount)   AS smallest_deal,
       MAX(amount)   AS largest_deal
FROM opportunities;

WHERE runs first, so you can aggregate a filtered set:

SELECT COUNT(*) AS open_deals, SUM(amount) AS open_pipeline
FROM opportunities
WHERE closed_at IS NULL;

COUNT in all its forms

So COUNT(*) - COUNT(closed_at) is the number of open deals.

Every aggregate except COUNT(*) skips NULLs

With values 10, 20 and NULL in a column x:

Neither average is wrong. Decide whether a missing value means “unknown” or “zero” first.

Across a row vs. down a column

This is the most useful habit in this lesson. Before writing a calculation, ask which direction you’re going:

• Across a row: + - * / between columns. No aggregate.
• Down a column: SUM, AVG, COUNT, MIN, MAX.

The two combine. Whatever is inside the parentheses happens on every row first, then the aggregate adds those results up:

SUM(amount * 1.03)        -- increase each row, then total
SUM(amount) * 1.03        -- total first, then increase the total
ROUND(SUM(amount), 2)     -- a function wrapped around an aggregate

SUM(a, b) is an error. An aggregate takes exactly one expression. Write SUM(a + b) or SUM(a) + SUM(b), and know that SUM(a + b) loses every row where b is NULL.

Empty results: SUM vs. COUNT

When no rows match, COUNT(*) returns 0 but SUM returns NULL. Wrap it so a report shows 0:

SELECT COALESCE(SUM(amount), 0) AS won_revenue
FROM opportunities
WHERE is_won AND owner_id = 999;   -- a new rep with no wins yet

MIN and MAX work on dates and text

MIN(created_at) is the oldest date. On text, MAX is the last value alphabetically, so MAX(stage) won’t give you the furthest stage in a sales process.

Try it

In one query, return two numbers: how many deals are closed, and how many are still open. Hint: you can’t filter both sets with one WHERE, so lean on how COUNT(column) treats NULL.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson