SQL Window Functions vs GROUP BY: When to Use Which

By the XLsheetAI Team · Updated August 14, 2026 · 10 min read

The one-sentence answer

Use GROUP BY when you want fewer rows than you started with, and a window function when you want the same rows with extra context attached. Both compute the same aggregates; they differ entirely in what happens to your result set, and that is what you should be choosing between.

What GROUP BY actually does to your rows

Five orders from two customers go into a GROUP BY customer_id and two rows come out. This is not a side effect, it is the point: you asked for one answer per customer, so the individual orders no longer exist in the result. You cannot then ask which order was the largest, or how each order compared to that customer's average, because there are no orders left to ask about.

SELECT customer_id, SUM(order_total) AS customer_total
FROM orders
GROUP BY customer_id;

-- 5 rows in, 2 rows out

That reduction is exactly what you want for a summary report. It is exactly what you do not want when the detail is the point.

What a window function does instead

A window function runs the same aggregate over a defined set of rows — the "window" — and writes the result onto every row in it. Nothing is collapsed.

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

-- 5 rows in, 5 rows out, each carrying its customer's total

PARTITION BY is the window's version of GROUP BY: it says which rows count as one group. Leave it out entirely and the window spans the whole result set, which is how you compute each row's share of the grand total in a single pass.

The test that settles it

Ask what your output needs to look like, not what calculation you are doing.

The four things only windows can do

1. Running totals

Add ORDER BY inside the OVER() and the window stops being the whole partition and becomes "every row up to this one":

SELECT
  txn_date,
  amount,
  SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_date) AS running_balance
FROM transactions;

That single clause change is the entire difference between a customer total and a running balance. GROUP BY has no equivalent, because a running total needs one output row per input row by definition.

2. Ranking

Three functions, and the difference between them is how they handle ties:

ROW_NUMBER() OVER (ORDER BY sales DESC)  -- 1, 2, 3, 4  (ties broken arbitrarily)
RANK()       OVER (ORDER BY sales DESC)  -- 1, 2, 2, 4  (ties share, then skip)
DENSE_RANK() OVER (ORDER BY sales DESC)  -- 1, 2, 2, 3  (ties share, no gap)

This is why "find the second highest salary" is such a common interview question: the naive answer works until two people tie for first, and choosing between RANK and DENSE_RANK is choosing what "second highest" means.

3. Comparing a row to its group

SELECT
  name,
  department,
  salary,
  salary - AVG(salary) OVER (PARTITION BY department) AS vs_dept_average
FROM employees;

With GROUP BY this needs a second query and a join back to the detail. With a window it is one expression.

4. Looking at the previous or next row

SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change_from_last_month
FROM monthly_revenue;

LAG and LEAD reach backwards and forwards in the ordered window. Month-over-month change, time between events, and gap detection all reduce to this one pattern.

The mistake everyone makes once

You write a ranking window, then try to filter on it:

-- This fails, in every database
SELECT product, category, sales,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
FROM products
WHERE rn <= 3;

SQL evaluates clauses in a fixed order: FROM, then WHERE, then GROUP BY, then HAVING, then window functions, then SELECT, then ORDER BY. When WHERE runs, rn has not been calculated yet — the column genuinely does not exist. HAVING does not help either, for the same reason.

The fix is to give the window a query of its own and filter the result:

WITH ranked AS (
  SELECT product, category, sales,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
)
SELECT product, category, sales
FROM ranked
WHERE rn <= 3;

Memorise this shape. "Top N per group" is one of the most common real reporting requests there is, and this is the answer to all of them.

Can you use both together?

Yes, and it is more useful than it sounds. A window function applied to a grouped query operates on the grouped rows:

SELECT
  region,
  SUM(revenue) AS region_revenue,
  SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER () AS pct_of_total
FROM sales
GROUP BY region;

The doubled SUM(SUM(revenue)) looks like a typo and is not. The inner SUM is the group aggregate; the outer one is the window running across all the grouped rows. Because windows are evaluated after GROUP BY, the window sees regions, not raw sales — which is exactly what a percentage-of-total needs.

What about performance?

As a rule, GROUP BY is cheaper. It reduces rows as it goes, while a window must retain every row and usually sort within each partition. If a summary is all you need, do not pay for a window to produce it.

That said, the comparison is often unfair, because the alternative to one window query is two queries and a join back to the detail — which is normally slower than the window and considerably harder to read. Compare the window against the query you would otherwise have written, not against the GROUP BY that answers a different question.

One practical note: PARTITION BY and ORDER BY inside a window benefit from an index in that same order, exactly as a regular ORDER BY does. On large tables that index is usually the difference between fast and unusable.

Coming to SQL from spreadsheets? XLsheetAI translates what you would have done with a pivot table into working SQL, explains the query line by line, and lets you practise the patterns until they are second nature.

Bottom line

Choose on the shape of the output. Fewer rows than you started with means GROUP BY. The same rows with more context means a window. Running totals, rankings, row-versus-group comparisons and previous-row lookups are windows only — and the moment you want to filter on one, reach for a CTE.

Related reading: Excel to SQL formula guide maps pivot tables and SUMIFS onto their SQL equivalents.