Learning Tips | Published Nov 16, 2026 | 8 min read

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.

SQL tutorial comparing GROUP BY with window functions

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

QuestionTypical approachResult rows
What is the total amount per region?GROUP BY regionOne 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

  1. Do you want one summary row per category? Use GROUP BY.
  2. Do you need the original details plus a total, rank or comparison? Use a window function.
  3. Which rows belong together? Define PARTITION BY.
  4. Does the calculation depend on sequence? Define ORDER BY and, for running calculations, an explicit frame.
  5. 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.

Explore the Java program   Ask for course guidance