0:00A table of orders can tell you what every single order was. It can't tell you what each customer spent.
0:06Aggregation is the step that gets you there. Many roes go in.. and one roe per group comes out.
0:13In this video, we will learn sequel aggregations.
0:15We'll start with the five functions that do the collapsing.
0:19Then the clause that decides what a group even is.
0:23Then two filters that look almost identical, and run at completely different times.
0:28And we'll finish by summing and counting only the roes that match a condition.. without throwing the other roes away.
0:35This is the table we'll use for the whole video. Eight orders, spread across three customers, and every order has a status and an amount.
0:43Now let's ask it some questions. We want a handful of summary numbers covering the whole set of orders.
0:49Count star just counts roes.
0:51Sum adds up a whole column.
0:53Average divides that sum by the count.
0:55And min and max pull out the two extremes.
0:58FROM orders finishes the statement. One pass over that table answers all five of these questions at once.
1:04And it reads all eight orders as one group. Nothing in the query asks sequel to split them by customer, or by status, or by anything else.
1:16So the whole group reduces to one roe.
1:19Running it fills that roe in. Six hundred and seventy across all eight orders, averaging eighty three seventy five.
1:26Eight orders went in, and one roe came out. That's what an aggregate does when nothing tells it where to divide.
1:33One roe for the whole table is rarely what you want.
1:37Usually you want one per customer, with that same count and total worked out for each of them.
1:43The select list is the part we already know. A column to say which customer, and the two aggregates.
1:49Then you tell sequel what to split on.
1:52And now there are three roes instead of one. Customer one oh one placed three orders worth two hundred and fifty.
1:59Back to the query, because there's a rule hiding in it. This column is what defines a group, so it's safe to select.
2:07And these two summarize the group, so they're safe too. Every column you select has to be one or the other.
2:13So here's a query that breaks it. Status is neither.
2:16Postgres refuses to guess.
2:18And it's right to refuse. Customer one oh one has two paid orders and one late one.
2:23There's only one roe coming out for this customer. So which of those three values would go in it?
2:29There's no honest answer, so we don't ask. Drop status, and the query means exactly what it says.
2:37Say we only want the customers who spent more than two hundred.
2:41Notice that's a test on a customer's total, not on any single order.
2:46The obvious move is to put that condition in a where clause.
2:49And it fails. Where runs before the groups exist, so at that moment there is no total to compare against. It's looking at one order at a time.
3:00The clause we need runs later, after the totals are built.
3:04That keeps one oh one and one oh two, and drops one oh three at a hundred and eighty.
3:09One thing catches people out. You have to write the sum a second time, instead of reusing the name you gave it up here.
3:16Now let's use both filters in the same query. Where goes first and throws away everything that isn't paid.
3:24Then the groups get built from whatever survived.. and only then do we test the totals.
3:30A hundred and seventy, and two hundred. Both filters, doing completely different jobs.
3:35So here's the order things happen in. One filter sees roes.
3:40Then the roes become groups.
3:42And the other filter only ever sees the groups. Same query, two different moments.
3:47Here's a harder ask.
3:49I want each customer's total spend, and separately, how much of that was late. Two numbers, one query.
3:56The total we already know how to write. It's the second number that needs something new.
3:60You put the condition inside the aggregate itself. It reads one order at a time, and on this data three of them match.
4:07Late orders hand back their amount. Everything else hands back a zero. And the sum just adds up whatever it's handed.
4:15Let's follow one customer through it. These three orders belong to one oh three, and two of them are late.
4:21First order is late, so it contributes its full sixty.
4:25Second one's late too. Ninety.
4:27The third one was paid on time.. so it contributes nothing.
4:31Add those up and you get a hundred and fifty of late spending.
4:36And here's what makes this different from a where clause. That paid order still counted toward the full total. Nothing was thrown away.. it just contributed zero to one of the columns.
4:47Summing conditionally was straightforward. Counting conditionally has a trap in it, and it's worth walking into on purpose.
4:54So this time we want counts rather than amounts. How many orders each customer placed, and how many of those were late.
5:02The first half is the easy one.
5:03Count star counts roes. It doesn't look at values at all.
5:07But give count an expression, and it only counts the ones that aren't null. Notice there's no else here.. so anything that isn't late comes back as nothing at all.
5:17So for customer one oh three, the two late orders each hand back a one, and the paid one hands back nothing.
5:24Two real values. Count says two.
5:27Now here's the trap. It feels natural to add an else zero, the way we did with sum.
5:33But zero is a value. It's not nothing.
5:35So count happily counts it, and the answer is wrong. Count never asked whether these were true.. it only asked whether they existed.
5:44If you'd rather write else zero, use sum instead of count.
5:48One for each match, zero for everything else, added up. Same answer, and this time the else zero does no harm.
5:55One last wrinkle. Customer one oh two has no late orders at all, so without an else, sum gets handed nothing but nulls.. and returns null.
6:04Add the else, and it returns a real zero. Which is usually what you want in a report.
6:10Both correct forms agree, all the way down.
6:13Let's put it all together.
6:15Four numbers for each customer. How many orders, how much of that was paid, how much was late, and how many refunds came back.
6:23The plain count of every order is the one that needs no condition at all.
6:27Then paid revenue. The condition picks the roes that count, and the sum around it does the adding.
6:33Late revenue is exactly the same expression. Only the status word changes.
6:38And refunds we want as a count, not an amount, so this one hands back a one and nothing else.
6:43Four metrics.. and one grouping clause underneath all of them. That's what keeps them comparable. Every number on a roe describes the same customer.
6:55Customer one oh two ordered twice. Two hundred paid, nothing late, one refund.
7:01So that's the whole toolkit.
7:03You can take a pile of roes and turn it into one roe per group.
7:06You know why sequel refuses some of the columns you might ask for, and what to do instead.
7:12You know which filter runs before the grouping and which one runs after.
7:16And you can build a column that only counts part of the data, without losing the rest of it.
7:23Here's one to try. Add a paid order count to that last query. Remember the difference. Count wants a null for the misses.. sum wants a zero.

SQL Aggregations

Created by Abhyuday DesaiPublished 3 Oct, v1Runs 7 min 41 sec

1 view

About this video

Most screening questions are built on this layer: collapse many order rows into one number per customer. One eight-row orders table powers every example, and it never leaves the screen.

You'll review how to:

Use COUNT, SUM, AVG, MIN and MAX, and why COUNT(*) and COUNT(column) differ
Group with GROUP BY and read the rule for what the SELECT list may contain
Put a condition in WHERE or in HAVING, and why the order matters
Compute several conditional metrics in one query with CASE inside SUM, and the COUNT-with-ELSE-0 trap

For engineers who know SELECT, WHERE and ORDER BY and want aggregation questions to feel routine again.

What changed

v103 Oct 2026abhydesai7 min 41 sec037acd5

First published version.

1 view0 likes on this version