COUNT, SUM, AVG, MIN, MAX

From many rows to one number

Every query so far returned rows, but most business questions want a number: how many users signed up this week, what did the store earn today, what is the average order size. Nobody answers those by reading a million rows. Dashboards, reports, and analytics are built almost entirely from this lesson. Aggregate functions boil many rows down to a single value:

This unit uses a cafe's orders table with columns customer, item, price. Averages often come out as long decimals like 5.83333333333333, so wrap them in ROUND(value, 2) to keep two decimal places.

Run the block: four questions, four one-row answers.

Aggregates respect WHERE

An aggregate runs after the row filter, so you can count or sum any slice of the table:

SELECT COUNT(*) FROM orders WHERE item = 'coffee';

First WHERE keeps only the coffee rows, then COUNT counts what is left. This one-two punch (filter, then aggregate) answers most everyday questions: revenue this month, users who never logged in, largest order over $10.

COUNT(*) vs COUNT(column)

COUNT(*) counts rows. COUNT(price) counts rows whose price is not NULL, because aggregate functions skip NULLs (SUM and AVG skip them too). The two disagree exactly when your data has gaps, and that is often the point: COUNT(*) asks "how many orders?", COUNT(price) asks "how many orders have a price on record?".

The block below adds a free tap water with a NULL price, then runs both counts.