GROUP BY vs PARTITION BY: Row Counts, Totals, and Frames

GROUP BY versus PARTITION BY: decide the output grain

GROUP BY forms summary rows. PARTITION BY inside a window function divides the rows available at that stage into sets while retaining their detail. They answer different questions and can be used together. This PostgreSQL 16 fixture has five sale rows, two regions, two sales on one date, and a missing date. The examples show exactly when the row count changes.

Use a five-row contract

Each id identifies a sale. Amounts are known, finite values in one currency. Region A has 10 and 20 on January 1 and 5 on January 3; region B has 7 on January 1 and 3 on January 2. The desired output grain determines the query: one row per region, one per sale, or one per observed region/day.

GROUP BY returns one summary per region

WITH sales(id,region,day,amount) AS (
 VALUES(1,'A',DATE '2026-01-01',10),(2,'A',DATE '2026-01-01',20),
 (3,'A',DATE '2026-01-03',5),(4,'B',DATE '2026-01-01',7),(5,'B',DATE '2026-01-02',3)
)
SELECT region,COUNT(*) AS sales,SUM(amount) AS total
FROM sales GROUP BY region ORDER BY region;

region | sales | total
A | 3 | 35
B | 2 | 10

Five inputs become two region summaries. A sale id no longer belongs to this output grain. Adding id to GROUP BY would change the question and return one group per sale. Group by stable dimensions rather than descriptive labels that can merge unrelated identities.

PARTITION BY can add region context to every sale

WITH sales(id,region,day,amount) AS (
 VALUES(1,'A',DATE '2026-01-01',10),(2,'A',DATE '2026-01-01',20),
 (3,'A',DATE '2026-01-03',5),(4,'B',DATE '2026-01-01',7),(5,'B',DATE '2026-01-02',3)
)
SELECT id,region,amount,
 SUM(amount) OVER(PARTITION BY region) AS region_total,
 ROUND(100.0*amount/NULLIF(SUM(amount) OVER(PARTITION BY region),0),2) AS share_pct
FROM sales ORDER BY id;

id | region | amount | region_total | share_pct
1 | A | 10 | 35 | 28.57
2 | A | 20 | 35 | 57.14
3 | A | 5 | 35 | 14.29
4 | B | 7 | 10 | 70.00
5 | B | 3 | 10 | 30.00

All five sale rows remain. There is no window ORDER BY, so SUM covers each whole partition. Repeated region_total is context, not additional revenue: summing that output column would count each regional total once per sale. Rounded percentages need not sum to exactly 100 in every dataset.

An ordered frame changes the calculation

WITH sales(id,region,day,amount) AS (
 VALUES(1,'A',DATE '2026-01-01',10),(2,'A',DATE '2026-01-01',20),
 (3,'A',DATE '2026-01-03',5),(4,'B',DATE '2026-01-01',7),(5,'B',DATE '2026-01-02',3)
)
SELECT id,region,day,amount,
 SUM(amount) OVER(PARTITION BY region ORDER BY day,id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales ORDER BY region,day,id;

id | region | day | amount | running_total
1 | A | 2026-01-01 | 10 | 10
2 | A | 2026-01-01 | 20 | 30
3 | A | 2026-01-03 | 5 | 35
4 | B | 2026-01-01 | 7 | 7
5 | B | 2026-01-02 | 3 | 10

Partition by region to accumulate within each region. Partitioning by day instead would reset the total every day. The id tie-breaker makes the two January 1 rows deterministic, and the explicit ROWS frame advances one sale at a time. A window ORDER BY defines the window calculation; the final ORDER BY defines output display.

Use GROUP BY before the window when the question needs daily rows

WITH sales(id,region,day,amount) AS (
 VALUES(1,'A',DATE '2026-01-01',10),(2,'A',DATE '2026-01-01',20),
 (3,'A',DATE '2026-01-03',5),(4,'B',DATE '2026-01-01',7),(5,'B',DATE '2026-01-02',3)
)
, daily AS (
 SELECT region,day,SUM(amount) AS amount FROM sales GROUP BY region,day
)
SELECT region,day,amount,
 SUM(amount) OVER(PARTITION BY region ORDER BY day
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_daily_total
FROM daily ORDER BY region,day;

region | day | amount | running_daily_total
A | 2026-01-01 | 30 | 30
A | 2026-01-03 | 5 | 35
B | 2026-01-01 | 7 | 7
B | 2026-01-02 | 3 | 10

Grouping produces four observed region/day rows; the window operates on those four rows. It does not restore the five original sale rows. Region A still has no January 2 row. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means seven available rows, not automatically seven calendar days. A complete daily spine and a missing-day policy are needed for a calendar-day average.

Filter and measure without changing the meaning

A WHERE filter in the same SELECT limits rows before the window sees them. If you need a full-history running balance but display only a recent interval, calculate the window in an earlier stage or supply an opening balance. HAVING can remove groups before a later window. Neither GROUP BY nor PARTITION BY is a universal faster substitute for the other because their outputs differ.

Inspect data size, sorting, aggregation, and memory on representative input. An index may support a filter or ordering, while a small table can reasonably scan directly. Window partitions are query-level groups; they are not physical table partitioning. Do not infer storage layout from the keyword.

Explain the row count as part of the answer

A concise answer is: “I need two summary rows, so I group by region,” or “I need each of five sales with its region total, so I use a window.” For a daily report, explain the intermediate four rows and the missing date. Test empty inputs, duplicate times, and zero totals; those reveal mistakes hidden by a single untied sample.

Continue with the relevant lesson

Use aggregate fundamentals for NULL and empty-set behavior, reporting aggregates for weighting, and running totals and opening balances for temporal frames. Primary references: grouped table expressions and window functions.

FAQ

Does PARTITION BY guarantee the original table’s row count? It preserves the rows presented to the window stage; earlier joins, filters, and grouping may already have changed them. Is a partition total a running total? Not without an appropriate ordered frame. Must I choose just one clause? No; grouped daily inputs followed by a window are common.