Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        SQL running total with window functions

        Calculate a running total in SQL with a window function: a runnable PostgreSQL example, a safe order for tied dates, and how it differs from GROUP BY.

        A person practicing on a laptop in a quiet workspace

        SQL window functions calculate across related rows while keeping each original row in the result. To build a running total, use SUM with OVER, separate groups with PARTITION BY, and specify both a stable calculation order and an explicit row frame. The PostgreSQL example below keeps every sale, restarts the total for each region, and handles sales that share a date.

        Calculations without grouped rows

        A window calculation adds a result to each row instead of reducing several rows to one summary row. For a sales report, you can show an individual sale and the region’s accumulated sales on the same line.

        GROUP BY serves a different purpose: it produces one row per group. If you group sales by region and sum their amounts, the individual sale records disappear from the output. A window total preserves those records.

        Window functions have several common uses. Aggregate functions such as SUM and AVG calculate totals and averages. Ranking functions such as ROW_NUMBER and RANK assign sequence numbers or positions. Offset functions such as LAG and LEAD read values from earlier or later rows. Supported functions and syntax vary by database.

        This tutorial uses PostgreSQL. You'll build one running total, change the calculation to a whole-region total, and test what happens when two sales have the same date.

        Four sales records

        The starting data has four fictional sales across two regions, including two East sales on the same date. The shared date is deliberate: it exposes an ordering mistake that a sample with unique dates would hide.

        order_id | region | sale_date  | amount
        1        | East   | 2026-01-05 | 40
        2        | East   | 2026-01-05 | 60
        3        | East   | 2026-01-06 | 30
        4        | West   | 2026-01-05 | 80

        Define the required result before writing the query:

        The running-total rules

        • Keep all four sales in the output.
        • Start a separate total for each region.
        • Process each region’s sales by sale date, then by the unique order identifier.
        • Include the current sale in its running total.

        The order identifier breaks a tie when two sales share a date. For this example, lower identifiers come first. In your own data, choose an ordering rule that matches the report’s purpose rather than assuming an identifier always represents event time.

        Each query uses VALUES to supply the records directly. A common table expression, introduced by WITH, gives that temporary query result the name sales. You can run each complete query in PostgreSQL without creating a table.

        Practice what this post teaches

        A running total by region

        The running total uses SUM over a regional window, ordered by date and identifier, with a frame that ends at the current row. Copy and run the complete query:

        WITH sales (order_id, region, sale_date, amount) AS (
          VALUES
            (1, 'East', DATE '2026-01-05', 40),
            (2, 'East', DATE '2026-01-05', 60),
            (3, 'East', DATE '2026-01-06', 30),
            (4, 'West', DATE '2026-01-05', 80)
        )
        SELECT order_id, region, sale_date, amount,
               SUM(amount) OVER (
                 PARTITION BY region
                 ORDER BY sale_date, order_id
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
               ) AS running_total
        FROM sales
        ORDER BY region, sale_date, order_id;

        Read the window definition from the inside out. SUM adds the amounts. OVER makes SUM a window calculation rather than a grouped aggregate. PARTITION BY splits the input into separate groups, called partitions, so East sales never contribute to the West total.

        The ORDER BY inside OVER defines the calculation sequence. The window frame defines which rows contribute to the calculation for the current row. Here, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means all rows from the start of the current region through the current sale.

        The final ORDER BY has a separate job: it controls how the output is displayed. A window’s calculation order doesn't guarantee the displayed order, so keep the final ORDER BY when you want a predictable report.

        order_id | region | sale_date  | amount | running_total
        1        | East   | 2026-01-05 | 40     | 40
        2        | East   | 2026-01-05 | 60     | 100
        3        | East   | 2026-01-06 | 30     | 130
        4        | West   | 2026-01-05 | 80     | 80

        Check the additions by hand. East starts at 40, reaches 100 after the second sale, and finishes at 130. West starts at 80 rather than continuing from East’s 130 because West has its own partition.

        After checking the output, use Practice: window functions on The Work Gym to practice writing window calculations yourself.

        Whole-region totals

        A whole-region total repeats the region’s final sum beside every sale. Remove the ordering and frame inside OVER, but keep PARTITION BY:

        WITH sales (order_id, region, sale_date, amount) AS (
          VALUES
            (1, 'East', DATE '2026-01-05', 40),
            (2, 'East', DATE '2026-01-05', 60),
            (3, 'East', DATE '2026-01-06', 30),
            (4, 'West', DATE '2026-01-05', 80)
        )
        SELECT order_id, region, amount,
               SUM(amount) OVER (PARTITION BY region) AS region_total
        FROM sales
        ORDER BY region, sale_date, order_id;
        order_id | region | amount | region_total
        1        | East   | 40     | 130
        2        | East   | 60     | 130
        3        | East   | 30     | 130
        4        | West   | 80     | 80

        Without ordering inside OVER, SUM uses the entire partition. The final ORDER BY still sorts the output, but it doesn't turn the regional sum into a running total.

        Choose the calculation from the required output. Use GROUP BY for one summary row per region. Use an unordered window total for each sale plus its regional total. Use an ordered window with the explicit row frame for each sale plus its accumulated total.

        If you need more practice with the underlying aggregate calculations, try COUNT, SUM, AVG, MIN and MAX.

        Ordering and filtering mistakes

        The most common running-total mistake is ordering only by a nonunique date and relying on PostgreSQL’s default frame. Run the following comparison query to see the effect:

        WITH sales (order_id, region, sale_date, amount) AS (
          VALUES
            (1, 'East', DATE '2026-01-05', 40),
            (2, 'East', DATE '2026-01-05', 60),
            (3, 'East', DATE '2026-01-06', 30),
            (4, 'West', DATE '2026-01-05', 80)
        )
        SELECT order_id, region, amount,
               SUM(amount) OVER (
                 PARTITION BY region
                 ORDER BY sale_date
               ) AS date_total
        FROM sales
        ORDER BY region, sale_date, order_id;
        order_id | region | amount | date_total
        1        | East   | 40     | 100
        2        | East   | 60     | 100
        3        | East   | 30     | 130
        4        | West   | 80     | 80

        With a window ORDER BY and no explicit frame, PostgreSQL’s default frame extends from the partition’s start through the current row’s last ordering peer. Peers are rows with equal values for every window ordering expression. Both January 5 East sales are peers, so both totals include 40 and 60.

        Equal totals aren't automatically wrong. If the requirement is an end-of-date balance beside every sale, both January 5 records should show 100. The limitation of the row-by-row method is that it requires a meaningful sequence; an arbitrary sequence can produce intermediate totals that have no business meaning.

        Filters before and after calculation

        WHERE removes source rows before window calculations run. If you remove the first East sale before calculating the window, the remaining East sales accumulate to 60 and then 90, not 100 and 130.

        PostgreSQL doesn't allow a window function directly in WHERE. To keep only rows whose running total exceeds a threshold, calculate the running total in a subquery or common table expression, then apply WHERE to that calculated column in an outer query. The outer filter changes the displayed rows without removing sales from the earlier calculation.

        Before trusting the original running-total report, check three properties: four output rows, an East sequence of 40, 100, and 130, and a separate West total of 80. Those checks catch lost records, tied-date behavior, and a missing regional partition.

        Common questions

        What is a window function in SQL? A window function calculates a value across related rows while preserving each row in the output. The OVER clause defines the calculation’s partition, ordering, and frame where applicable.

        How do window functions differ from GROUP BY? GROUP BY combines records into summary rows, such as one total per region. A window calculation can place the same regional total beside every individual sale without combining those sales into one row.

        When should you use SQL window functions? Use SQL window functions when you need individual records alongside running totals, group totals, rankings, or neighboring values. Choose GROUP BY instead when the output needs only summary rows.

        Why do two rows show the same running total? In PostgreSQL, a window ordered only by date uses a default frame that includes peers with the same date. For a stable row-by-row total, add a unique ordering key and specify a ROWS frame ending at the current row.

        Can you filter a window function result with WHERE? Not directly in the same query level in PostgreSQL. Calculate the window result in a subquery or common table expression, then filter the calculated column with WHERE in an outer query.

        Practice what this post teaches

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.