Aggregates and window functions

GROUP BY

Start with a report of ordered units for each product. An order line records a quantity, so counting lines is not enough: order 102 has one line for two mugs.

For the examples, line_facts names the result of attaching each order’s date and status to its lines. The order key is unique, so this join keeps one row per order line:

SELECT i.order_id, i.line_no, i.product_id,
       i.quantity, i.unit_price,
       i.quantity * i.unit_price AS line_value,
       o.placed_at, o.status
FROM order_items AS i
JOIN orders AS o ON o.order_id = i.order_id;

The download defines this query as a temporary view called line_facts. A view gives a query a name that another query can use like a table. It supplies the same five rows, without storing another copy of the facts.

SELECT product_id, COUNT(*) AS lines,
       SUM(quantity) AS units
FROM line_facts
GROUP BY product_id
ORDER BY product_id;

GROUP BY product_id puts lines with the same product ID together. Each group becomes one result row. P3’s two lines have quantities 1 and 2, so its group has two lines and three units.

The grouping columns define the result’s grain. Change them to product_id, status and each product’s paid and pending lines form separate groups:

SELECT product_id, status, SUM(quantity) AS units
FROM line_facts
GROUP BY product_id, status
ORDER BY product_id, status;

P3 now has a paid row for one unit and a pending row for two. P7 splits the same way, with two paid units and one pending. P9 has only a paid row. The report has five rows instead of three, even though its input has not changed. Adding a grouping column changes the question.

What belongs in a group row

A product summary can show the product ID because it is the same throughout the group. It can show a sum or count calculated from the group. It cannot simply show order_id: the P3 group contains orders 101 and 103, and neither is “the” order for that summary. PostgreSQL rejects that ungrouped column.

Putting the column inside MAX makes a different, valid request, but does not select an entire row:

SELECT product_id, MAX(quantity) AS largest_quantity,
       MAX(order_id) AS largest_order_id
FROM line_facts
GROUP BY product_id
ORDER BY product_id;

For P7, the largest quantity is 2, from order 102, while the largest order ID is 103, whose quantity is 1. The result P7, 2, 103 combines two independent maxima. To select a particular source row, we need an ordering and a row-selection rule; ranking will supply those below.

Names need similar care. Grouping only by a product’s name could combine distinct products that happen to share it. Use the product key. If the report needs the catalogue name too, join the completed product summary to products on that key. PostgreSQL also permits other columns from a table when its primary key is grouped, because that key determines them.

Grouping supplies no promised display order. The final ORDER BY product_id makes the output dependable. It does not change which lines belong to a group.

WHERE, HAVING and FILTER

The shop now wants to compare all ordered units with units on paid orders. “Paid” here means the order’s stored status, not a fresh calculation from its payments. There are three places a condition can act, and each changes a different population.

WHERE status = 'paid' removes pending lines before grouping. Every calculation then sees only the three paid lines. HAVING SUM(quantity) >= 3 instead keeps all five lines for grouping and rejects completed groups whose sum is too small. FILTER restricts the input to one aggregate, leaving other aggregates free to use the same rows.

The distinction becomes clearer when we apply each condition to the same five lines. Removing pending lines changes every sum; rejecting a small group changes which summaries survive; filtering one sum can keep both paid-only and all-order totals in the same report.

SELECT product_id, SUM(quantity) AS units
FROM line_facts
WHERE status = 'paid'
GROUP BY product_id
ORDER BY product_id;

This gives P3 1 unit, P7 2 and P9 1. There is no longer enough information in these groups to calculate the all-order totals of 3, 3 and 1.

SELECT product_id, SUM(quantity) AS units
FROM line_facts
GROUP BY product_id
HAVING SUM(quantity) >= 3
ORDER BY product_id;

This gives P3 and P7 with 3 units each. P9’s one-unit group was formed but fails the condition. WHERE SUM(quantity) >= 3 cannot express this at the same query level: WHERE decides which lines reach the sum.

SELECT product_id, SUM(quantity) AS all_units,
       SUM(quantity) FILTER (WHERE status = 'paid')
         AS paid_units
FROM line_facts
GROUP BY product_id
ORDER BY product_id;

Now P3 has 3 units in all and 1 in paid orders. P7 has 3 in all and 2 in paid orders. P9 has 1 in all and 1 in paid orders. Put a condition in WHERE when it should restrict the whole report’s input. Put it in FILTER when only that calculation should use the smaller population. Use HAVING when the decision needs a completed group’s value.

Counts, NULLs and empty inputs

A count needs a unit as much as a sum does. These four calculations over the same five lines give three different answers:

SELECT COUNT(*) AS lines,
       COUNT(quantity) AS known_quantities,
       COUNT(DISTINCT product_id) AS products,
       SUM(quantity) AS units
FROM line_facts;

quantity cannot be null in the order-line schema, so its count equals the row count here. That equality can disappear after an outer join. Count customers’ orders, including Edda, who has none:

SELECT c.customer_id, COUNT(*) AS joined_rows,
       COUNT(o.order_id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;

Mina has two joined rows and two orders. Tariq has one and one. Edda has one joined row but zero orders. Her preserved row has null order columns: COUNT(*) counts it, while COUNT(o.order_id) does not. The order’s non-null key is what makes the second count a count of matches.

A missing measurement is not a zero

For a separate measurement example, suppose the tea package weighs 65 grams, the teapot 820 grams, and the mug has not yet been measured. The mug’s weight is NULL. The temporary product_weights table holds these three product IDs and their nullable package_weight_g measurements.

SELECT COUNT(*) AS products,
       COUNT(package_weight_g) AS measured,
       SUM(package_weight_g) AS measured_grams,
       AVG(package_weight_g) AS mean_measured_grams
FROM product_weights;

The result is 3 products, 2 measured, 885 measured grams and a mean of 442.5 grams. SUM and AVG ignore the null weight. The mean divides by two known measurements, not three products. It does not estimate the missing mug’s weight.

For the mug alone, the non-null count is zero and the sum is null. COALESCE(SUM(package_weight_g), 0) would display zero, but would turn “no known weight” into a number that looks measured. The still-due report could deliberately treat no recorded payments as zero paid; that reporting policy does not make an unknown measurement zero.

The population matters for averages even without nulls. Across these order lines, AVG(unit_price) is 16.80: each line gets equal weight. The mean price per ordered unit is SUM(quantity * unit_price) / SUM(quantity), or 108.50 / 7 = 15.50. The quantity-two lines must contribute twice to that second mean.

No rows, or one row containing NULL?

No order in the starting data is cancelled. Compare these queries:

SELECT COUNT(*) AS lines, SUM(quantity) AS units
FROM line_facts
WHERE status = 'cancelled';

SELECT product_id, SUM(quantity) AS units
FROM line_facts
WHERE status = 'cancelled'
GROUP BY product_id;

The first returns one row: lines = 0, units = NULL. Without grouping columns, the aggregate summarises the whole input even when it is empty. The second returns no rows: there is no product group to summarise. A final HAVING can reject a scalar aggregate’s one row too.

If zero units is the intended meaning for an empty report, COALESCE(SUM(quantity), 0) supplies it. In the grouped query it cannot invent absent product rows. To show every product, including those without lines, start from products and attach the summaries with a left join.

Keeping each row with a window function

Suppose the shop wants to show how much each line contributes to its product’s ordered units. A grouped result has already lost the line’s identity. A window function keeps that identity and makes the other lines available for the calculation.

SELECT order_id, line_no, product_id, quantity,
       SUM(quantity) OVER (
         PARTITION BY product_id
       ) AS product_units,
       ROUND(100.0 * quantity / SUM(quantity) OVER (
         PARTITION BY product_id
       ), 1) AS percent_of_product
FROM line_facts
ORDER BY product_id, order_id, line_no;

OVER makes this SUM a window calculation. PARTITION BY product_id divides the input into separate sets called partitions. With no ordering inside OVER, this sum uses the whole product partition for each line. P3’s two lines both receive 3, but keep their own quantities of 1 and 2.

The percentage divides the current line’s quantity by that repeated total. Multiplying by 100.0 gives numeric division before rounding to one decimal place. With integer operands, 1 / 3 truncates to zero in PostgreSQL. The positive-quantity constraint and a non-empty partition keep this denominator above zero.

Do not sum product_units down the displayed result to get a shop total. P3’s 3 appears twice and P7’s 3 appears twice. The repeated number is context for a line, not another independently ordered quantity.

Which rows the window sees

A window sees the input left by this query’s joins, filtering and grouping. Add WHERE status = 'paid' to the window query and its P3 partition contains only the one paid tea line, with a total of 1. The pending line cannot contribute after it has been removed.

A window over product summaries sees products instead of lines:

WITH product_totals AS (
  SELECT product_id, SUM(quantity) AS units
  FROM line_facts
  GROUP BY product_id
)
SELECT product_id, units, SUM(units) OVER () AS all_units
FROM product_totals
ORDER BY product_id;

The common table expression produces three rows with units 3, 3 and 1. SUM(units) OVER () has no partition columns, so all three rows share a partition and receive 7. There are still three rows. A window does not restore earlier rows, and putting it after a join that multiplied quantities does not repair that multiplication.

Ranking and ties

The three product summaries can now become a ranking. Tea and mugs tie at three units; the teapot follows at one. A business rank should recognise that tie, while a numbered list still needs to choose which row comes first.

WITH product_totals AS (
  SELECT product_id, SUM(quantity) AS units
  FROM line_facts
  GROUP BY product_id
)
SELECT product_id, units,
       ROW_NUMBER() OVER (
         ORDER BY units DESC, product_id
       ) AS position,
       RANK() OVER (ORDER BY units DESC) AS place,
       DENSE_RANK() OVER (ORDER BY units DESC) AS score_place
FROM product_totals
ORDER BY units DESC, product_id;

ROW_NUMBER gives each row a different position. Its ordering includes product ID, making P3 first and P7 second deterministically. RANK gives both the same first place and then skips to third. DENSE_RANK also gives both first place but puts the next distinct score in second place.

Rows equal on all of a window’s ordering expressions are peers. For these ranks, tea and mugs are peers because only units DESC defines equality. Adding product ID inside RANK would break that tie and give places 1, 2, 3. Keep a display tie-breaker in the final ORDER BY when it must not change the business rank.

There are therefore two different orders in this query. An ORDER BY inside OVER determines the calculation. The final ORDER BY determines how the finished rows are displayed. Supply both when both matter.

Window frames

An ordered window can calculate a running total. But what does “up to this row” mean when several rows have the same ordering value?

Return to one row per order line and add its recorded value, quantity * unit_price. Order 101’s two lines share placed_at, as do order 103’s. They are timestamp peers.

SELECT order_id, line_no, line_value,
       SUM(line_value) OVER (
         ORDER BY placed_at
       ) AS cumulative_value
FROM line_facts
ORDER BY placed_at, order_id, line_no;

The partition contains all five lines because there is no PARTITION BY. Its ordering is the timestamp. For a frame-sensitive function such as SUM, the frame chooses the rows within that partition which contribute at the current row. The default ordered frame reaches from the start through the last peer of the current row.

For the first line, that default includes both lines with the same timestamp. A cumulative ROWS frame can instead add one line at a time, provided its ordering resolves the tie.

The default sum begins at 39.50, not 4.50, because both lines of order 101 contribute immediately. It stays at 39.50 for the second line and reaches 79.50 at order 102. It then jumps to 108.50 for both lines of order 103.

To accumulate one line at a time, choose an order that identifies every line and state the frame explicitly:

SELECT order_id, line_no, line_value,
       SUM(line_value) OVER (
         ORDER BY placed_at, order_id, line_no
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_value
FROM line_facts
ORDER BY placed_at, order_id, line_no;

The ordered key now includes order_id, line_no, so no two lines tie. ROWS measures positions in that order. The sums become 4.50, 39.50, 79.50, 99.50 and 108.50. A ROWS frame without a tie-breaker would leave the order of equal-timestamp lines unspecified; the final display order alone would not fix their running sums.

If every line should receive the entire partition’s value, omit the window ordering as before, or state a frame from UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING. Every line then receives 108.50. Adding an ordering to a whole-partition sum without considering its frame can accidentally turn it into a running sum.

Rows, values and peer groups

Bounded frames make the unit of distance explicit. At line 103/1, these three frames include different rows:

ROWS counts individual row positions. GROUPS counts peer groups, so one preceding group means the preceding timestamp’s lines as well as all peers at the current timestamp. RANGE measures distance in the ordering value. Here the timestamp range begins three days before 20 October at 10:15 UTC, so order 102 on 14 October is outside it.

A bounded RANGE with an offset in PostgreSQL needs one ordering expression. A date or timestamp order uses an interval offset; a numeric order uses a compatible numeric offset. A preceding-row count does not mean a calendar period: quiet days add no rows, and busy days can add many. Frames also stop at partition ends. The first line’s ROWS BETWEEN 1 PRECEDING AND CURRENT ROW contains only that line, because there is no previous one.

What LAST_VALUE calls “last”

LAST_VALUE(line_value) reads the last row of the current frame. With the complete line order and the default frame, line 101/1 gets 4.50: it is the last row in its own frame. To get the partition’s final line value, 9.00, for every line, extend the frame to the end:

SELECT order_id, line_no,
       LAST_VALUE(line_value) OVER (
         ORDER BY placed_at, order_id, line_no
       ) AS default_last,
       LAST_VALUE(line_value) OVER (
         ORDER BY placed_at, order_id, line_no
         ROWS BETWEEN UNBOUNDED PRECEDING
                  AND UNBOUNDED FOLLOWING
       ) AS partition_last
FROM line_facts
ORDER BY placed_at, order_id, line_no;

The function name does not choose the frame. An explicit whole-partition frame is what makes the second expression reach order 103’s final line.

Previous and next rows

Some comparisons need one neighbouring row rather than a total. At one row per order, LAG can attach the previous order’s timestamp:

SELECT customer_id, order_id, placed_at,
       LAG(placed_at) OVER (
         PARTITION BY customer_id
         ORDER BY placed_at, order_id
       ) AS previous_order_at
FROM orders
ORDER BY customer_id, placed_at, order_id;

Mina’s second order receives 12 October at 09:30 UTC. Her first has no previous row and receives null. Tariq’s order also receives null: partitioning by customer keeps Mina’s history out of his. LEAD makes the equivalent lookup forwards.

The optional third argument of LAG(value, 1, default) applies when there is no previous row. It does not replace a null value stored in a previous row. If a nullable measurement is being compared, use LAG on a non-null row key as well when the report needs to distinguish “no previous observation” from “previous observation has no value”. PostgreSQL respects nulls rather than skipping them.

LAG and LEAD look for neighbouring rows in the partition’s order; they do not use the aggregate’s frame boundary. Even with a frame restricted to CURRENT ROW, Mina’s order 103 can still read order 101 through LAG. Choose a frame for a frame-sensitive calculation, and an offset for a neighbouring-row lookup.

Filtering a calculated result

To select rows by a window result, calculate it first and filter in an outer query. A WHERE at the same level runs too early to test that window result in PostgreSQL.

WITH product_totals AS (
  SELECT product_id, SUM(quantity) AS units
  FROM line_facts
  GROUP BY product_id
), ranked AS (
  SELECT product_id, units,
         ROW_NUMBER() OVER (
           ORDER BY units DESC, product_id
         ) AS position,
         RANK() OVER (ORDER BY units DESC) AS place,
         DENSE_RANK() OVER (ORDER BY units DESC) AS score_place
  FROM product_totals
)
SELECT product_id, units
FROM ranked
WHERE position <= 2
ORDER BY units DESC, product_id;

position <= 2 returns exactly two rows here: P3 and P7. The product key resolves their tie for the numbered list. place <= 1 also returns both, but for a different reason: they share first place. score_place <= 2 returns all three products, because the teapot has the second distinct score. “Top two” needs that choice before it becomes SQL.

Keeping the filter outside also preserves the ranking’s population. If a filter removed products before ranking, the places would describe only the remaining products. The same applies to a line’s percentage: calculate its comparison with the intended partition before removing lines merely to shorten the displayed result.

A report is ready to trust when its row meaning, contributing population, ties and frame all match the question. Trace a line into its sum, check an absent match such as Edda, and inspect a tie such as P3 and P7. Those small cases expose errors a plausible total can hide. How much work the database does to produce the result, and how to continue through a changing result in pages, are separate questions.