SQL Window Functions vs GROUP BY
Compare GROUP BY and SQL window functions with sales examples, per-group totals, rankings, running totals and filtering tips.
GROUP BY and SQL window functions both calculate across rows, but they preserve different result shapes. This guide uses a sales table to show when to summarize rows into groups and when to add rankings or running totals while keeping each detail row visible.
GROUP BY reduces rows to one row per group
Suppose a sales table contains one row per transaction, with region, sale_date and amount. To calculate total sales per region, use an aggregate with GROUP BY:
SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region;The result has one row per region. Individual transactions are no longer shown because the query has summarized them.
A window function adds a calculation to each row
If you need each transaction and its region's total side by side, use an aggregate as a window function:
SELECT
sale_date,
region,
amount,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;The output keeps the transaction rows and repeats the region total on each row in that region. PARTITION BY region defines which rows contribute to each calculation; it does not collapse them.
Rank rows inside each group
Window functions are useful for questions such as “rank transactions within each region” or “show the three largest transactions per region.” Add a window ordering:
SELECT
sale_date,
region,
amount,
ROW_NUMBER() OVER (
PARTITION BY region
ORDER BY amount DESC, sale_date, sale_id
) AS position
FROM sales;The extra tie-breakers make row numbering deterministic when amounts are equal, assuming sale_date and sale_id together distinguish the order you want. To return the top three, calculate the row number in a subquery and filter outside it:
SELECT sale_date, region, amount, position
FROM (
SELECT
sale_date,
region,
amount,
ROW_NUMBER() OVER (
PARTITION BY region
ORDER BY amount DESC, sale_date, sale_id
) AS position
FROM sales
) AS ranked_sales
WHERE position <= 3;In PostgreSQL, window functions are evaluated after WHERE, GROUP BY and HAVING, so a subquery or another supported query layer is needed to filter on the computed window result.
Calculate a running total
Add ORDER BY inside OVER when the calculation should progress through rows in a sequence:
SELECT
sale_date,
region,
amount,
SUM(amount) OVER (
PARTITION BY region
ORDER BY sale_date, sale_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM sales;The explicit ROWS frame means the total starts at the first row in the region and includes rows through the current row in that ordering. Including a stable tie-breaker avoids ambiguity when multiple transactions share a date.
Compare the result shape
| Question | Typical approach | Result rows |
|---|---|---|
| What is the total amount per region? | GROUP BY region | One row per region |
| What is each sale and its region total? | SUM() OVER (PARTITION BY region) | One row per sale |
| What is each sale's position within its region? | ROW_NUMBER() OVER (...) | One row per sale with a rank |
| What is the cumulative amount by date? | SUM() OVER (ORDER BY ... frame) | One row per sale with a running value |
Common mistakes
- Expecting GROUP BY to keep detail rows. Grouping produces one output row per group; use a window function when each original row must remain.
- Putting a window function directly in WHERE. In PostgreSQL, calculate it in a subquery and filter in the outer query.
- Ranking without a tie-breaker. If ordering values tie, row numbering among those peers may not be stable. Add a unique key when deterministic order matters.
- Forgetting that the query filters input rows first. Rows removed by WHERE are not available to the window calculation.
- Using an unclear running-total frame. Specify a frame and ordering that match the intended sequence, especially when duplicate ordering values are possible.
Decision checklist
- Do you want one summary row per category? Use GROUP BY.
- Do you need the original details plus a total, rank or comparison? Use a window function.
- Which rows belong together? Define PARTITION BY.
- Does the calculation depend on sequence? Define ORDER BY and, for running calculations, an explicit frame.
- Will you filter on the calculated rank or value? Put the window calculation in a subquery, then filter outside it.
For practice, compare the number of transaction rows with the number of result rows after each query. This simple check often reveals whether you need a grouped summary or a windowed calculation.
Explore the SQL course details or the Jaipur Engineers course catalog for structured learning options.
Official PostgreSQL reference
The PostgreSQL window functions tutorial explains OVER, PARTITION BY, ordering and why window calculations preserve rows. SQL details can differ across database engines, so check the documentation for the system you use.
Want a structured Java learning path?
Explore Jaipur Engineers course options to connect Java fundamentals, debugging, backend development and project practice in a guided learning plan.