Okay, but what’s the average?
Counting customers went well enough that finance is back with a follow-up: “Okay, but what’s the average order value?”
One line: AVG(amount) FROM orders. Should be exactly like COUNT.
It is exactly like COUNT — same idea, different math. Go ahead and run it.
Running…
A number comes back. Looks like an answer. But look at the orders schema again — there’s a status column: completed, pending, refunded, cancelled.
That average just quietly included every cancelled order, every refund, and every order that's still sitting pending. Is that really 'the average order value' finance means?
No — they mean money we actually collected. I need to filter down to completed orders first.
Exactly. WHERE runs before AVG ever sees a row. Filter first, then aggregate.
AVG joins COUNT as an aggregate function — and it’s got siblings. SUM adds a column up. MIN and MAX find the smallest and largest value. All four take a whole column of numbers and turn it into one number.
Turn a column of numbers into one number.
Start with the edges of the completed orders — cheapest and priciest:
SELECT MIN(amount) AS cheapest, MAX(amount) AS priciest
FROM orders
WHERE status = 'completed';| cheapest | priciest |
|---|---|
| 24.99 | 199.99 |
Now the numbers finance actually asked for — total revenue and the real average order value, both scoped to orders NOVA actually got paid for:
SELECT SUM(amount) AS total_revenue, AVG(amount) AS avg_order_value
FROM orders
WHERE status = 'completed';Running…
That’s the real revenue picture: the total NOVA actually collected, the average completed order was worth, and the cheapest and priciest completed orders on record — with every cancelled, refunded, and pending order excluded before any of the math runs.
Try it: Do MIN and MAX actually change if you drop the WHERE status = 'completed' filter?
Run your query to see results here.
They don’t — same $24.99 and $199.99 either way, because the cheapest and priciest orders in the whole table happen to both be completed already. But run SUM or AVG without the filter and they’ll move: every cancelled, refunded, or pending dollar gets folded back in. Whether a filter changes your answer depends on the data, not the function — which is exactly why you check status instead of assuming.
Finance wants three numbers about completed orders, in a single query: the total revenue, the average order value, and the single most expensive order NOVA has processed.
Hint 1
Only orders that actually count as collected revenue belong in this — which value in the status column is that, and which clause keeps only those rows?
Hint 2
SUM, AVG, and MAX can all live in the same SELECT list. Each one produces its own column in a single result row.
Hint 3
SELECT SUM(amount) AS total_revenue, AVG(amount) AS avg_order_value, MAX(amount) AS priciest_order FROM orders WHERE status = 'completed';
Solution
Running…
None of these functions know what a “cancelled” order is, or that a refund isn’t real revenue. They just aggregate whatever rows reach them. Getting the right number was never about the aggregate function — it was about the WHERE clause deciding which rows got there in the first place.
SUM, AVG, MIN, and MAX all collapse a column into one number — but which rows they see depends entirely on WHERE. Filter first, aggregate second, or you’re averaging money you never actually made.