Your orders table has one row per order, but almost nobody asks a question shaped like that. They ask how many orders each customer placed, what the total spend was in each city, which product sold the most last month. Every one of those answers takes many rows and squeezes them into one number per group. That squeeze is aggregation, and the clause that decides which rows share a number is GROUP BY. It is the moment SQL stops listing data and starts summarising it.

Part 7 taught you to join two tables so a customer name sits next to an order amount. This part takes that joined result and collapses it into totals and counts. Then it shows you the other half of the story, window functions, which do the same arithmetic without throwing your rows away, so you can put a running total or a rank right beside each original row. Grouping and windowing are the two ways SQL turns raw records into the summary a manager actually reads.

From many rows to one number

An aggregate function is one that reads a whole column of values and returns a single answer. The five you will use almost every day are COUNT, which tells you how many, SUM, which adds them, AVG, which averages them, and MIN and MAX, which find the smallest and largest. Run one on its own with no grouping and it treats the entire table as a single pile. Here is the same five-row orders table from Part 7, and a query that summarises the whole thing at once.

The orders table. Five rows, one per order, the raw material every query below summarises.

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

Result: one row. order_count 5, total_amount 4750, avg_amount 950, smallest 300, largest 2000.

Common failure: mixing an aggregate and a plain column in the same SELECT with no GROUP BY, such as SELECT customer_id, SUM(amount) FROM orders. PostgreSQL rejects it, because it cannot show one customer_id next to a total built from five different customers. The moment you want a total per something, you need GROUP BY.

Each function handles the empty marker NULL, which you met in Part 6, in a specific way worth memorising. COUNT(*) counts every row including those with NULLs. COUNT of a named column counts only the rows where that column has a value. SUM, AVG, MIN and MAX all skip NULLs entirely. That last point trips people up: an AVG over a column with missing values divides by the count of present values, not the row count, so a NULL is not treated as a zero.

The five aggregates on the sample orders. Learn the NULL column, it is the source of most surprising totals.

Group rows with GROUP BY

A total for the whole table is rarely the question. You want a total per customer, per city, per month. GROUP BY names the column whose equal values define a group, and the database then runs your aggregates once inside each group instead of once over everything. Ask for total spend per customer and GROUP BY customer_id, and you get one row back for each distinct customer_id, with the SUM computed from only that customer’s orders.

SELECT customer_id,
       COUNT(*)    AS order_count,
       SUM(amount) AS total_amount,
       AVG(amount) AS avg_amount
FROM orders
GROUP BY customer_id
ORDER BY total_amount DESC;

Result: four rows, one per customer_id. Customer 1003 leads with 2000, then 1001 with 1650 across two orders, then 1004 with 800, then 1099 with 300.

Five order rows collapse into four customer rows. Only customer 1001, with two orders, shows a count above one.

Here is the rule that confuses every beginner once, so learn it now. Every column in your SELECT that is not inside an aggregate function must appear in the GROUP BY. The reason is physical: after grouping, each output row stands for many input rows, so a column the database has not grouped on could hold several different values, and it has no way to pick one. Group by customer_id and you may select customer_id and any aggregate, but not order_date, because a customer’s orders span several dates. The picture below shows the collapse happening.

Filter groups with HAVING

You already know WHERE filters rows. But WHERE runs before grouping, so it cannot see a total, because the totals do not exist yet. To filter on an aggregate, say only customers who spent more than 1000, you need HAVING, which runs after the groups are formed and the aggregates computed. Think of it as a WHERE for groups. The two clauses often appear together: WHERE trims the raw rows going in, HAVING trims the summarised rows coming out.

SELECT customer_id,
       SUM(amount) AS total_amount
FROM orders
WHERE amount > 0
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total_amount DESC;

Result: two rows. Customer 1003 with 2000 and customer 1001 with 1650. Customers 1004 and 1099 fall out because their totals sit below 1000.

Common failure: putting an aggregate in WHERE, as in WHERE SUM(amount) > 1000. PostgreSQL throws an error, because at WHERE time no sum has been computed. Move that test to HAVING and it works. The reverse also holds: a plain row filter like amount > 0 belongs in WHERE, not HAVING, so the database can drop those rows before doing the heavier grouping work.

flowchart TD
  A[FROM and JOIN build the rows] --> B[WHERE filters single rows]
  B --> C[GROUP BY forms groups]
  C --> D[Aggregates compute per group]
  D --> E[HAVING filters groups]
  E --> F[SELECT and window functions]
  F --> G[ORDER BY sorts the output]

The three shapes of COUNT

COUNT looks simple but has three forms that answer three different questions, and picking the wrong one gives a wrong number that looks perfectly reasonable. COUNT(*) counts rows, full stop, NULLs included. COUNT(column) counts only rows where that column is not NULL, which matters the instant your data has gaps. COUNT(DISTINCT column) counts the distinct values, ignoring repeats, which is how you answer how many different customers ordered rather than how many orders there were.

Keep every row with window functions

GROUP BY has one cost: it destroys your rows. Once you group by customer, the individual orders are gone, replaced by summaries. But often you want the summary and the detail together, each order shown next to that customer’s running total, or each order ranked against the rest. That is exactly what a window function does. It computes an aggregate over a set of related rows, called the window, but returns a value for every original row instead of collapsing them.

The syntax is the aggregate you already know, followed by OVER and a description of the window. Inside OVER, PARTITION BY splits the rows into groups the same way GROUP BY does, and ORDER BY sets the order within each group, which matters for running totals and ranks. Leave PARTITION BY out and the whole result is one window. Here every order keeps its row, but gains its customer’s total in a new column.

SELECT order_id,
       customer_id,
       amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders
ORDER BY customer_id, order_id;

Result: five rows, none lost. Both of customer 1001’s orders show customer_total 1650 beside their own amount; the single orders for 1003, 1004 and 1099 show 2000, 800 and 300.

Common failure: trying to filter on a window function in WHERE, such as WHERE customer_total > 1000. Window functions are computed after WHERE and HAVING, so they are only allowed in SELECT and ORDER BY. To filter on one, wrap the query in an outer SELECT and filter there, a subquery, which Part 20 revisits.

Ranking and running totals

Two jobs make window functions worth learning: ranking rows and building running totals. For ranking there are three functions that look alike and differ on ties. ROW_NUMBER gives every row a unique position, 1, 2, 3, even when values tie. RANK gives tied rows the same number, then skips, so two firsts are followed by a third. DENSE_RANK also ties but does not skip, so two firsts are followed by a second. Which you want depends on whether gaps after a tie make sense for your report.

A running total is a SUM with an ORDER BY inside the window. Because the default window frame runs from the start of the partition up to the current row, each row’s SUM includes everything up to and including it, which is the definition of a running total. Order the orders by date and you get cumulative spend over time, the shape of every revenue-to-date chart you have seen.

SELECT order_id,
       order_date,
       amount,
       RANK() OVER (ORDER BY amount DESC)      AS amount_rank,
       SUM(amount) OVER (ORDER BY order_date)  AS running_total
FROM orders
ORDER BY order_date;

Result: five rows in date order. running_total climbs 1200, 1650, 2450, 4450, 4750. amount_rank labels order 9003 (2000) as 1, order 9001 (1200) as 2, and so on down to order 9005 (300) as 5.

Common failure: writing a running total with no ORDER BY inside OVER. Without it there is no notion of before and after, so SUM returns the same grand total on every row instead of a climbing figure. The ORDER BY inside OVER is what makes it cumulative; do not confuse it with the ORDER BY at the end of the query, which only sorts the final display.

Group when you want a summary, window when you want detail

Reach for GROUP BY when the report is a summary and you are happy to lose the individual rows: sales per month, average order value per city, number of tickets per agent. Reach for a window function when you need each row to survive and carry a group-level number beside it: a rank, a running total, a share of the whole, a comparison to the previous row. That single decision, summary or detail, covers the vast majority of analytical queries you will write in your first year, and getting it right up front saves you from rewriting the query halfway through.

You can now count, sum and average across groups, filter those groups with HAVING, and add ranks and running totals without discarding your rows. Combined with the joins from Part 7, that is most of the SQL a working analyst writes on any given day. Part 9 changes gears from querying clean data to fixing dirty data, handling the missing values, duplicates and inconsistent labels that every real dataset carries, because the tidy five-row table you practised on is not what lands in your inbox. Keep the Data Analyst guide open as your map, and revisit Part 7 if the join underneath a grouped query needs a refresher.

This week, load the five orders into a sandbox and write all three: the GROUP BY per customer, the same totals as a window SUM beside each order, and a running total ordered by date. Watching five rows become four and then stay five teaches the difference between grouping and windowing faster than any explanation.

References

• Aggregate Functions, PostgreSQL Documentation
• Window Functions, PostgreSQL Documentation
• Aggregate Functions Reference, PostgreSQL Documentation

About The Author

Dr. Pranay Jha

Dr. Pranay Jha is a Cloud and AI Consultant with 18+ years of experience in hybrid cloud, virtualization, and enterprise infrastructure transformation. He specializes in VMware technologies, multi-cloud strategy, and Generative AI solutions. He holds a PhD in Computer Applications with research focused on Cloud and AI, has published multiple research papers, and has been a VMware vExpert since 2016 and a VMUG Community Leader.

See author's posts