COUNT, SUM, AVG

Why it matters

A manager rarely wants to see 40,000 order rows. They want "how many orders did we ship last month, what did they bring in, and what is a typical order worth?". Those are aggregate questions: many rows go in, one summary value comes out. Aggregates are the basis of every report, dashboard and KPI you will build.

The five core aggregates

Example orders table:

SELECT COUNT(*)    AS order_count,
       SUM(amount) AS total,
       AVG(amount) AS average,
       MIN(amount) AS smallest,
       MAX(amount) AS largest
FROM orders;

The whole table collapses into one row.

How NULLs affect aggregates

Look closely: COUNT(*) is 5, but the average is 140.5 / 4, not / 5. Every aggregate except COUNT(*) ignores NULL values. Order 5 has no amount yet, so it is skipped by SUM, AVG, MIN, MAX and COUNT(amount).

That is usually what you want, but be deliberate. If a missing amount should count as zero, say so with COALESCE(amount, 0) (covered in this module's third lesson). The two choices give different averages, and a report should state which one it uses.

Filtering before aggregating

WHERE runs before the aggregate, so it decides which rows are summarised:

SELECT COUNT(*) AS shipped_orders,
       SUM(amount) AS shipped_revenue
FROM orders
WHERE status = 'shipped';

Cancelled and pending orders never reach SUM.

Counting distinct values

COUNT(DISTINCT column) counts unique non-null values. "How many different customers placed an order?":

SELECT COUNT(DISTINCT customer) AS customers FROM orders;

Ana appears twice, so the answer is 4, not 5.

Rounding the output

AVG often returns long decimals. Round for presentation:

SELECT ROUND(AVG(amount), 2) AS avg_order FROM orders WHERE status = 'shipped';

The shipped amounts are 25, 40 and 60, so the average is 41.666..., shown as 41.67. Round in the final output only. If you round values before adding them up, small errors add up.

Mixing aggregates with plain columns

This looks reasonable but is a mistake:

SELECT customer, SUM(amount) FROM orders;

You asked for one summary row (the SUM) and also a per-row value (customer). Which customer should appear? PostgreSQL, SQL Server and (by default) MySQL reject the query. SQLite runs it and picks a value from an arbitrary row, which silently gives a misleading answer. The fix is GROUP BY, covered in the next lesson: "one row per customer".

Aggregates in expressions

Aggregates can be combined with arithmetic. Share of revenue from shipped orders:

SELECT ROUND(
         100.0 * SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END)
               / SUM(amount), 1) AS pct_shipped
FROM orders;

You will meet CASE properly later in this module. Note the 100.0: it keeps the division in decimals.

An empty result still returns a row

If no rows match the WHERE, COUNT(*) returns 0 but SUM, AVG, MIN and MAX return NULL. A dashboard that shows "NULL" instead of "0" for a quiet day is a common bug; COALESCE(SUM(amount), 0) fixes it.

Common mistakes

• Expecting COUNT(column) to count every row. It skips NULLs. Use COUNT(*) for rows.
• Averaging with missing values without thinking. Decide whether a NULL means "unknown" (skip it) or "zero" (COALESCE).
• Putting an aggregate in WHERE. WHERE SUM(amount) > 100 is an error, because WHERE runs before aggregation. Group-level filters use HAVING (next lesson).
• Selecting a plain column next to an aggregate without GROUP BY.
• Integer averages in some engines. In PostgreSQL and SQL Server, AVG of an integer column can behave differently than you expect (SQL Server truncates to an integer). Cast to a decimal when precision matters.

Quick recap

• COUNT, SUM, AVG, MIN, MAX turn many rows into one value.
• All but COUNT(*) ignore NULLs.
• WHERE filters rows before they are aggregated.
• COUNT(DISTINCT col) counts unique values.
• Round only the final output.

Try it

Open the Do tab and write a one-row summary of an online shop's shipped orders.