Grouping, Aggregates, and Window Functions

Grouping, aggregates, and window functions let PostgreSQL answer questions about sets of rows without making the application fetch every row and count by hand. The practical outcome is control over row shape: a grouped aggregate collapses many input rows into one row per group, while a window function keeps the original rows and adds calculations that can see neighboring rows in a defined window.

In the querying part of this PostgreSQL course, this lesson sits after filtering and joins because summaries depend on a clear input relation. By the end, you should be able to predict whether a query changes cardinality, choose between GROUP BY and OVER, read the important parts of the syntax, and diagnose common errors such as accidental double counting or non-deterministic rankings.

Purpose and Row Shape

PostgreSQL evaluates a query as a pipeline of relational operations. For this topic, the key distinction is whether the operation reduces rows. GROUP BY partitions the input into groups using one or more expressions. Aggregate functions such as count, sum, avg, min, and max consume each group and return one value per group. The final result contains one row for each distinct grouping key, plus any aggregate values you requested.

A window function also partitions rows, but it does not collapse them. A call such as sum(amount) OVER (PARTITION BY customer_id ORDER BY order_date) returns a running total on every order row. The original order row remains visible, and the calculated value is attached beside it. This is why windows are ideal for rankings, running totals, moving averages, and comparing a row with the previous row.

Mechanism Inside PostgreSQL

At execution time, aggregate queries are usually implemented with either a hash aggregate or a sorted aggregate. A hash aggregate builds an in-memory hash table keyed by the grouping expressions, then updates transition state for each aggregate as rows arrive. For sum(amount), the state is essentially the current sum for that group. For avg(amount), PostgreSQL tracks enough state to produce the final average, such as sum and count. If the hash table grows beyond available work memory, the plan may spill work to temporary files or the planner may choose a different strategy.

A sorted aggregate first receives rows ordered by the grouping keys, either because an index already provides that order or because a sort node produces it. The executor can then finish one group before moving to the next. Sorted aggregation can use less memory for many distinct groups, but it may pay for sorting. The planner estimates row counts, distinct group counts, available indexes, and costs before choosing the plan.

Window functions need their own ordering discipline. PostgreSQL forms partitions from PARTITION BY, orders rows inside each partition when ORDER BY is present, and evaluates the function over a frame. The default frame surprises many people: with an ordered aggregate window, the frame is from the start of the partition through the current row and any peers with the same ordering value. That is useful for running totals, but for whole-partition totals you should often specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING or omit ORDER BY in the window.

Syntax Anatomy

The basic grouped aggregate shape is SELECT group_key, aggregate(argument) FROM source WHERE row_filter GROUP BY group_key HAVING group_filter ORDER BY result_expression. WHERE filters individual rows before grouping. HAVING filters groups after aggregates have been computed. Every selected expression must either be grouped, be aggregated, or be functionally dependent on grouped columns in a way PostgreSQL can prove.

The window shape is function(arguments) OVER (PARTITION BY keys ORDER BY sort_keys frame_clause). PARTITION BY is optional; without it, the whole result is one partition. ORDER BY is required for meaningful ranks and lag-style comparisons. A frame clause such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits which rows inside the ordered partition are visible to aggregate window functions. Ranking functions such as row_number, rank, and dense_rank use partition and order, but not the frame in the same way aggregate windows do.

Example 1: One Row Per Category

This first example creates a small sales table and summarizes revenue by region. The grouped query returns two rows because there are two distinct regions. Notice that WHERE status = 'paid' removes refunded rows before the groups are formed.

DROP TABLE IF EXISTS lesson_sales;
CREATE TABLE lesson_sales (
    id integer PRIMARY KEY,
    region text NOT NULL,
    salesperson text NOT NULL,
    sold_on date NOT NULL,
    amount numeric(10,2) NOT NULL,
    status text NOT NULL CHECK (status IN ('paid', 'refunded'))
);

INSERT INTO lesson_sales (id, region, salesperson, sold_on, amount, status) VALUES
    (1, 'East', 'Ada', DATE '2026-01-03', 120.00, 'paid'),
    (2, 'East', 'Ada', DATE '2026-01-04', 80.00, 'paid'),
    (3, 'East', 'Ben', DATE '2026-01-05', 40.00, 'refunded'),
    (4, 'West', 'Cy', DATE '2026-01-03', 200.00, 'paid'),
    (5, 'West', 'Dee', DATE '2026-01-07', 50.00, 'paid');

SELECT region,
       count(*) AS paid_orders,
       sum(amount) AS paid_revenue
FROM lesson_sales
WHERE status = 'paid'
GROUP BY region
ORDER BY region;

The deterministic output is East | 2 | 200.00 and West | 2 | 250.00. The refunded East row is absent before aggregation, so it does not affect either count(*) or sum(amount). If you need all regions including those with zero paid orders, start from a regions table and left join the filtered sales.

Example 2: Filtering Groups With HAVING

The next query asks for salespeople whose paid revenue is at least 150. This cannot be expressed with WHERE sum(amount) >= 150 because WHERE runs before sum exists. HAVING is the correct post-group filter.

SELECT salesperson,
       count(*) AS paid_orders,
       sum(amount) AS paid_revenue
FROM lesson_sales
WHERE status = 'paid'
GROUP BY salesperson
HAVING sum(amount) >= 150
ORDER BY salesperson;

The output is Ada | 2 | 200.00 and Cy | 1 | 200.00. Dee has a paid order, but her group total is only 50.00, so HAVING removes that group. This example also shows why aliases are mainly for the output list and ORDER BY; write the aggregate expression itself in HAVING for portability and clarity.

Example 3: Ranking Without Losing Rows

Suppose the report needs every paid sale plus its rank inside the salesperson’s region. A grouped query would lose the individual sale rows. A window function keeps each sale and adds a computed rank. The tiebreaker id makes the order deterministic when two sales have the same amount.

SELECT id,
       region,
       salesperson,
       amount,
       row_number() OVER (
           PARTITION BY region
           ORDER BY amount DESC, id
       ) AS region_sale_position
FROM lesson_sales
WHERE status = 'paid'
ORDER BY region, region_sale_position;

The East rows are Ada’s 120.00 sale with position 1 and Ada’s 80.00 sale with position 2. The West rows are Cy’s 200.00 sale with position 1 and Dee’s 50.00 sale with position 2. If the business wants ties to share a place, use rank() or dense_rank() instead of row_number(). With rank(), a tie for first makes the next row rank third; with dense_rank(), the next row is second.

Example 4: Running Totals and Frames

A running total is an aggregate used as a window function. The following query sums paid revenue within each region as dates advance. The frame says to include all earlier physical rows in the ordered partition through the current row.

SELECT id,
       region,
       sold_on,
       amount,
       sum(amount) OVER (
           PARTITION BY region
           ORDER BY sold_on, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_region_revenue
FROM lesson_sales
WHERE status = 'paid'
ORDER BY region, sold_on, id;

The running totals are East 120.00 then 200.00, and West 200.00 then 250.00. The explicit ROWS frame avoids peer-row surprises when multiple sales share the same date. If you used ORDER BY sold_on without a unique tiebreaker, rows on the same day could be peers, and the default frame could include more than the current physical row.

Design Choices and Trade-offs

Choose GROUP BY when the answer is naturally one row per category, account, day, tenant, or other key. Choose a window when the answer must preserve detail rows while adding context. You can combine them by aggregating in a subquery and then applying windows to the grouped result, but keep each layer explicit so readers know which row shape they are looking at.

Be careful after joins. Aggregates summarize the rows produced by the FROM clause, not the conceptual entities in your head. Joining orders to line items multiplies order rows by line count. If you then count orders, use count(DISTINCT orders.id) or aggregate line items first, depending on the question. DISTINCT can be correct, but it may be more expensive and can hide a modeling mistake.

For performance, indexes help most when they support filtering, joining, or required ordering. An index on (region, sold_on, id) can help a window query that partitions and orders by those columns after a selective filter, but PostgreSQL still has to process the qualifying rows. Large summaries are often faster when narrowed by date or pre-aggregated into reporting tables, provided the refresh process is correct.

Failure Modes and Troubleshooting

A common symptom is ERROR: column must appear in the GROUP BY clause or be used in an aggregate function. The cause is selecting a non-aggregated column while asking PostgreSQL to produce one row per group. Diagnose by writing the intended output grain in one sentence, then mark every selected column as grouped, aggregated, or removed. Correct it by grouping the column, aggregating it with a meaningful function, or moving detail columns to a window query.

Another symptom is totals that are too large after a join. The cause is usually row multiplication. Run SELECT key, count(*) FROM joined_query GROUP BY key ORDER BY count(*) DESC against the join before aggregating. If counts exceed the intended grain, aggregate the many-side table first or join on a more precise key. Do not patch the report with DISTINCT until you know which duplicate rows are legitimate.

A third symptom is unstable pagination or changing ranks between runs. The cause is an incomplete ORDER BY in the window. Rows with equal sort keys can be returned in different physical orders. Diagnose by checking whether the ordering columns uniquely identify a sequence inside each partition. Correct it by adding a stable tiebreaker such as the primary key.

For slow aggregate or window queries, use EXPLAIN (ANALYZE, BUFFERS) on representative data. Look for full scans that read far more rows than needed, large sorts, disk spills, or bad row estimates. Corrections may include better predicates, statistics refresh with ANALYZE, an index matching filter and order columns, or restructuring into staged aggregation.

Security, Performance, and Reliability

Aggregates can leak information when exposed carelessly. A tenant report must filter by tenant before grouping, not after producing global totals. Row-level security policies and parameterized predicates should be tested with aggregate queries because a single total can reveal the existence or approximate size of data outside the caller’s scope.

Performance risk comes from memory-heavy hash tables, sorts, and broad windows. Set sensible statement timeouts for interactive systems, avoid unbounded ad hoc reports on transactional tables, and review execution plans before adding dashboard queries that run frequently. Reliability improves when reports define a deterministic grain and order, because downstream exports and audits can reproduce the same result from the same snapshot.

Hands-on Lab

Prerequisites: a PostgreSQL session where you can create and drop a scratch table. Use a local database or temporary training schema, not a shared production schema. The lab verifies grouping, group filtering, rankings, and running totals with deterministic data.

1. Run the setup and region summary from Example 1. Verify that exactly two rows return and that West has 250.00 paid revenue.
2. Run the HAVING query from Example 2. Verify that Dee is absent because the filter applies after grouping.
3. Run the ranking query from Example 3. Verify that each region restarts at position 1 and that ORDER BY amount DESC, id creates a stable sequence.
4. Run the running-total query from Example 4. Verify that each region’s second paid row equals the final region total.
5. Run EXPLAIN (ANALYZE, BUFFERS) before the Example 4 query on your local data. With this tiny table the plan is not important for speed, but you should be able to identify the scan, sort, and window nodes.

Cleanup is DROP TABLE IF EXISTS lesson_sales;. If you added indexes or used a dedicated schema, drop those objects as well. In a transaction-based lab, you can alternatively run everything between BEGIN and ROLLBACK.

Assessment Exercises

1. A report needs each order row plus the customer’s lifetime spend. Would you use GROUP BY, a window function, or both? Explain the row shape you expect.
2. Given an orders table joined to order_items, why might count(*) overstate order count? Write a diagnostic query that proves whether multiplication happened.
3. Rewrite a query that incorrectly uses WHERE sum(amount) > 1000. Explain why the corrected clause runs later in the query pipeline.
4. Two sales in the same region have the same amount. Compare the output of row_number, rank, and dense_rank when a primary-key tiebreaker is omitted.
5. For a dashboard that shows daily revenue by tenant, list two predicates or policies that must be tested to prevent cross-tenant aggregate leakage.

Summary

Grouped aggregates answer one-row-per-group questions by collapsing partitions of the input. Window functions answer row-preserving analytical questions by calculating across a partition and frame. The most important design habit is to name the intended grain before writing syntax. Once the grain is clear, choose pre-group filters with WHERE, post-group filters with HAVING, deterministic ordering for windows, and execution-plan checks for large data.