How do I show each group's total next to every row in SQL?
Put each branch's total next to each of its months, without the 35 rows turning into 3.
Free course. Your Maestro, an AI, .
You're working with data from a small chain of fondas (family-style lunch counters) in Puebla, Mexico, with three branches: Centro, Norte and Sur. You have each month's sales for 2025 (synthetic data, made up for the course), and you want to see every month and, on the same row, its branch's total for the year, so you can tell how much that month contributed.
GROUP BY gets you the total, but you lose the months: 35 rows turn into 3. A window function gives you both at once, and you'll write one yourself in the next few minutes.
First, two quick questions. Go with your gut: it's not a test, and it shows you (and your Maestro) where you're starting from.
Note: Are GROUP BY or WITH new to you?
This course picks up where SQL for Data People leaves off: it assumes you already use SELECT, WHERE, ORDER BY, SUM, GROUP BY and queries with WITH. If any of those sounds unfamiliar, that course teaches them from scratch.
Check yourself: The ventas_mensuales table has 35 rows from 3 branches. You write SELECT sucursal, mes, SUM(ventas) OVER (PARTITION BY sucursal) FROM ventas_mensuales, with no GROUP BY. How many rows does it return?
Check yourself: You number each branch's months with ROW_NUMBER() OVER (PARTITION BY sucursal ORDER BY mes) and want to keep only row number 1. Can you put that condition in WHERE?
Try it
The query below uses GROUP BY and returns one row per branch. Run it first to see the 3 rows. Then change it:
- Remove the
GROUP BY. - Select
sucursal,mesandventas, and addSUM(ventas) OVER (PARTITION BY sucursal) AS total_sucursal. - Sort by
sucursal, mes.
OVER (...) is what turns SUM into a window function. PARTITION BY sucursal tells it which rows to look at for each row: the ones from the same branch. When the result table turns green, you've got it. If you get an error or still see 3 rows, ask your Maestro.
Return all 35 rows with sucursal, mes, ventas and a totalsucursal column holding its branch's total for the year, in that column order. Use SUM(ventas) OVER (PARTITION BY sucursal) instead of GROUP BY, and sort by sucursal, mes. You're done when you see 35 rows and every Centro r
Your turn
Return all 35 rows with sucursal, mes, ventas and a total_sucursal column holding its branch's total for the year, in that column order. Use SUM(ventas) OVER (PARTITION BY sucursal) instead of GROUP BY, and sort by sucursal, mes. You're done when you see 35 rows and every Centro row says 2174100.
Tables: ventas_mensuales (sucursal, mes, ventas)
ventas_mensuales · 35 rows
| Column | Type |
|---|---|
| sucursal Nombre de la sucursal: Centro, Norte o Sur. | VARCHAR |
| mes Primer día del mes que resume la fila. | DATE |
| ventas Ventas del mes, en pesos. | BIGINT |
First rows
| sucursal | mes | ventas |
|---|---|---|
| Centro | 2025-01-01 | 165100 |
| Centro | 2025-02-01 | 155700 |
| Centro | 2025-03-01 | 178700 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
What you just saw is the idea behind the whole course: a window function computes a value for each row by looking at other rows, and it doesn't collapse anything. The 35 rows are still there; each one gained a column.
Row by row
Here are a few rows from the solution, as they came out in Maestro's sandbox:
| sucursal | mes | ventas | total_sucursal |
|---|---|---|---|
| Centro | 2025-01-01 | 165100 | 2174100 |
| Centro | 2025-02-01 | 155700 | 2174100 |
| Norte | 2025-01-01 | 128900 | 1705500 |
| Sur | 2025-01-01 | 105500 | 1294800 |
For each row, the query finds the rows from the same branch, adds them up and writes the result on that row. That's why all 12 Centro rows repeat 2174100 and all 11 Sur rows repeat 1294800: Sur is missing June, and the sum only sees the rows that exist.
The "window" is the set of rows the function looks at to compute one row's value. With PARTITION BY sucursal, the window for a Norte row is the 12 Norte rows. With an empty OVER (), the window is the whole table.
Now you can compare each month with its total: ventas / total_sucursal. You'll do that on the next page. Do you have a table at work where you want each group's total next to every row? Tell your Maestro about it.
How it shows up in an interview: "show each employee next to the average salary of their department." People who don't know window functions write a GROUP BY in a subquery and join it back to the table. With AVG(salario) OVER (PARTITION BY departamento) it's one line, and none of the employee rows get lost.
Try this at work tomorrow: find one of your queries that does a GROUP BY only to join the result back to the original table. That join is exactly what a window saves you.
Three questions about what's ahead
This page doesn't explain these three: they're here to see what you already know. Go with your gut. They come back at the end of the course, worded differently.