SQL Server Window Aggregates: Running Totals and Moving Averages (2026)

Window aggregate functions are one of the most powerful analytical tools in SQL Server's T-SQL arsenal. Unlike traditional GROUP BY aggregates that collapse rows, window aggregates compute values across a sliding window of rows while preserving every row in the result set. The result: running totals, 7-day rolling averages, year-to-date sums, percentage-of-total breakdowns, and full financial dashboards — all in a single SELECT statement.

This article covers the complete window aggregate toolkit: syntax, frame clauses, common pitfalls (the RANGE vs ROWS trap that silently produces wrong running totals), and a practical multi-KPI financial dashboard query you can adapt immediately.

1. Window Aggregate Syntax: SUM / AVG / COUNT / MIN / MAX OVER()

Every window aggregate follows the same template:

aggregate_function(expression)
OVER (
    [ PARTITION BY partition_expression ]
    [ ORDER BY order_expression ]
    [ ROWS | RANGE BETWEEN frame_start AND frame_end ]
)

• PARTITION BY — resets the aggregate for each distinct value, like a GROUP BY that does not collapse rows. Omit it to treat the entire result set as one partition.
• ORDER BY — defines the logical sequence within the partition. Required for running totals and moving averages.
• Frame clause — controls which rows relative to the current row are included in the calculation. Default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when ORDER BY is present — but this default is dangerous for running totals (covered in Section 3).

The five aggregate functions work identically in window context:

SELECT
    order_date,
    amount,
    SUM(amount)   OVER(ORDER BY order_date) AS running_total,
    AVG(amount)   OVER(ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
    COUNT(*)      OVER()                    AS total_rows,
    MIN(amount)   OVER(ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_min,
    MAX(amount)   OVER(ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_max
FROM sales;

All five window aggregates can run in a single pass over the data, making them extremely efficient compared to correlated subqueries or self-joins.

2. Running Total: Cumulative Sales and Bank Balance

A running total accumulates from the first row to the current row, ordered by a date or sequence column. The canonical pattern uses SUM … OVER(ORDER BY date ROWS UNBOUNDED PRECEDING):

-- Cumulative daily sales running total
SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue)
        OVER (ORDER BY order_date
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
FROM daily_sales
ORDER BY order_date;

Bank balance running sum — credits increase, debits decrease:

SELECT
    txn_date,
    txn_id,
    description,
    amount,                         -- positive = credit, negative = debit
    SUM(amount)
        OVER (PARTITION BY account_id
              ORDER BY txn_date, txn_id
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
FROM account_transactions
WHERE account_id = 1001
ORDER BY txn_date, txn_id;

PARTITION BY account_id ensures each account gets its own running balance, resetting at the first transaction of each account. The secondary sort on txn_id breaks ties within the same date — critical for correctness.

3. The RANGE vs ROWS Pitfall: Why Duplicates Break Running Totals

This is the single most common source of silent, wrong running totals in SQL Server. When you write SUM(amount) OVER(ORDER BY date) without an explicit frame clause, SQL Server uses the default:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE CURRENT ROW means "all rows with the same ORDER BY value as the current row." If two sales share the same order_date, SQL Server includes both in each of their "current row" frames — so both get the combined total rather than individual running totals.

-- BAD: implicit RANGE — wrong when dates repeat
SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS wrong_running_total   -- RANGE default!
FROM sales;

-- GOOD: explicit ROWS — always correct
SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS correct_running_total
FROM sales;

Demonstration with duplicate dates:

-- Sample data: two rows on 2026-01-02
-- date        amount   RANGE total   ROWS total
-- 2026-01-01  100      100           100
-- 2026-01-02  200      500  ← wrong  300  ← correct
-- 2026-01-02  200      500  ← wrong  500
-- 2026-01-03  150      650           650

4. Moving Average: 7-Day Rolling Average

A moving (rolling) average smooths short-term fluctuations by averaging the current row plus a fixed number of preceding rows. The ROWS BETWEEN N PRECEDING AND CURRENT ROW frame makes this precise:

-- 7-day rolling average of daily sales
SELECT
    sale_date,
    daily_sales,
    AVG(daily_sales * 1.0)        -- multiply by 1.0 to avoid integer truncation
        OVER (
            ORDER BY sale_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS avg_7d
FROM daily_sales_summary
ORDER BY sale_date;

The frame 6 PRECEDING AND CURRENT ROW includes up to 7 rows total (6 before + current). For the first 6 rows of the result set where fewer than 7 rows exist, SQL Server automatically uses whatever rows are available — no special handling needed.

5. Moving Average Variations: 30-Day, 12-Month, Excluding Current Row

Extend the same pattern to any window size:

SELECT
    sale_date,
    daily_sales,

    -- 30-day rolling average
    AVG(daily_sales * 1.0)
        OVER (ORDER BY sale_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
        AS avg_30d,

    -- 12-month rolling average (monthly data)
    AVG(monthly_revenue * 1.0)
        OVER (ORDER BY month_start ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
        AS avg_12mo,

    -- Trailing 7 days EXCLUDING current row (useful for lag comparisons)
    AVG(daily_sales * 1.0)
        OVER (ORDER BY sale_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING)
        AS avg_prior_7d,

    -- Centered 7-day average (3 before, current, 3 after)
    AVG(daily_sales * 1.0)
        OVER (ORDER BY sale_date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
        AS avg_centered_7d

FROM daily_sales_summary
ORDER BY sale_date;

The "excluding current row" variant (7 PRECEDING AND 1 PRECEDING) is especially useful for comparing today's value against the prior-week average to detect anomalies without including the current observation in its own baseline.

6. Year-to-Date (YTD) with PARTITION BY YEAR

YTD resets at the start of each calendar year. PARTITION BY YEAR(date) achieves this automatically:

SELECT
    order_date,
    YEAR(order_date)  AS yr,
    MONTH(order_date) AS mo,
    daily_revenue,

    -- Year-to-date cumulative revenue
    SUM(daily_revenue)
        OVER (
            PARTITION BY YEAR(order_date)
            ORDER BY order_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS ytd_revenue,

    -- Month-to-date cumulative revenue
    SUM(daily_revenue)
        OVER (
            PARTITION BY YEAR(order_date), MONTH(order_date)
            ORDER BY order_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS mtd_revenue

FROM daily_sales
ORDER BY order_date;

Using PARTITION BY YEAR(order_date) means the running sum resets to zero on January 1 of each new year. Adding MONTH(order_date) to the partition key further resets for MTD. No WHERE filter or self-join is required.

7. Running Count: Total Rows Without a Subquery

COUNT(*) OVER() with no ORDER BY or frame returns the total row count of the partition — identical for every row in the result. This is the fastest way to get a total count for pagination without a separate COUNT(*) subquery:

-- Pagination: return page 3 (rows 21–30) plus total count in one query
SELECT *
FROM (
    SELECT
        p.product_id,
        p.product_name,
        p.price,
        COUNT(*) OVER () AS total_products,   -- same value on every row
        ROW_NUMBER() OVER (ORDER BY p.product_name) AS rn
    FROM products p
    WHERE p.category = 'Electronics'
) x
WHERE x.rn BETWEEN 21 AND 30
ORDER BY x.rn;

The application reads total_products from the first returned row to render pagination controls. This eliminates the classic dual-query pattern (one SELECT COUNT(*) + one SELECT … OFFSET … FETCH), roughly halving round-trips for paginated list endpoints.

-- Running count: how many rows have been seen so far (ordered)
SELECT
    order_date,
    order_id,
    COUNT(*) OVER (ORDER BY order_date, order_id ROWS UNBOUNDED PRECEDING) AS running_count
FROM orders
ORDER BY order_date, order_id;

8. Percentage of Total: Contribution Analysis with PARTITION BY

Window aggregates eliminate the need for a self-join or CTE just to compute each row's share of the group total:

SELECT
    region,
    product_category,
    sales_amount,

    -- Category total within each region
    SUM(sales_amount) OVER (PARTITION BY region, product_category)
        AS category_total,

    -- Region total
    SUM(sales_amount) OVER (PARTITION BY region)
        AS region_total,

    -- % of region
    ROUND(
        100.0 * sales_amount
        / NULLIF(SUM(sales_amount) OVER (PARTITION BY region), 0),
    2) AS pct_of_region,

    -- % of grand total
    ROUND(
        100.0 * sales_amount
        / NULLIF(SUM(sales_amount) OVER (), 0),
    2) AS pct_of_grand_total

FROM regional_sales;

NULLIF(…, 0) guards against division-by-zero if a partition total is ever zero. The unpartitioned SUM(sales_amount) OVER() returns the grand total for every row, allowing the percentage calculation inline.

9. Cumulative Distribution and Quartile Share Analysis

You can build a cumulative percentage distribution — how much of the total each successive row accounts for — using a running sum divided by the grand total:

SELECT
    customer_id,
    total_spent,

    -- Cumulative % of revenue (ordered by spend descending = Pareto analysis)
    ROUND(
        100.0
        * SUM(total_spent) OVER (ORDER BY total_spent DESC
                                  ROWS UNBOUNDED PRECEDING)
        / NULLIF(SUM(total_spent) OVER (), 0),
    2) AS cumulative_pct,

    -- Quartile label (top 25%, 50%, 75%, rest)
    CASE
        WHEN NTILE(4) OVER (ORDER BY total_spent DESC) = 1 THEN 'Q1 Top 25%'
        WHEN NTILE(4) OVER (ORDER BY total_spent DESC) = 2 THEN 'Q2'
        WHEN NTILE(4) OVER (ORDER BY total_spent DESC) = 3 THEN 'Q3'
        ELSE 'Q4 Bottom 25%'
    END AS quartile

FROM customer_lifetime_value
ORDER BY total_spent DESC;

This Pareto-style query instantly shows whether 20% of customers drive 80% of revenue — the classic business intelligence check — without any application-side processing.

10. Running MIN / MAX: Peak and Trough Tracking

Running minimum and maximum track the all-time low and all-time high seen up to each date — useful for stock price analysis, service SLA tracking, and sensor data:

SELECT
    price_date,
    closing_price,

    -- All-time high up to this date
    MAX(closing_price)
        OVER (ORDER BY price_date ROWS UNBOUNDED PRECEDING) AS all_time_high,

    -- All-time low up to this date
    MIN(closing_price)
        OVER (ORDER BY price_date ROWS UNBOUNDED PRECEDING) AS all_time_low,

    -- Distance from all-time high (drawdown %)
    ROUND(
        100.0 * (closing_price
                 - MAX(closing_price) OVER (ORDER BY price_date ROWS UNBOUNDED PRECEDING))
        / NULLIF(MAX(closing_price) OVER (ORDER BY price_date ROWS UNBOUNDED PRECEDING), 0),
    2) AS drawdown_pct,

    -- 52-week high and low (rolling 1-year window, ~252 trading days)
    MAX(closing_price)
        OVER (ORDER BY price_date ROWS BETWEEN 251 PRECEDING AND CURRENT ROW) AS high_52w,
    MIN(closing_price)
        OVER (ORDER BY price_date ROWS BETWEEN 251 PRECEDING AND CURRENT ROW) AS low_52w

FROM stock_prices
WHERE ticker = 'MSFT'
ORDER BY price_date;

11. Window Aggregates vs GROUP BY: When to Use Each

Performance: Window aggregates with good supporting indexes are typically faster than equivalent self-joins or correlated subqueries. However, GROUP BY on a well-indexed column is generally faster than COUNT(*) OVER() for a pure count, because the query plan can use a stream aggregate vs a window spool. Profile both with SET STATISTICS IO, TIME ON for your actual data volumes.

12. Index Design for Window Aggregates

The ideal supporting index for a window aggregate mirrors the PARTITION BY key(s) as leading columns, followed by the ORDER BY column(s), with any remaining SELECT columns as INCLUDEd columns:

-- Window query we want to optimise:
SELECT
    account_id, txn_date, amount,
    SUM(amount) OVER (PARTITION BY account_id
                      ORDER BY txn_date
                      ROWS UNBOUNDED PRECEDING) AS running_balance
FROM account_transactions;

-- Supporting covering index:
CREATE INDEX IX_Transactions_Account_Date_Amount
    ON account_transactions (account_id, txn_date)    -- PARTITION BY, ORDER BY
    INCLUDE (amount);                                  -- projected column

Key principles:

• PARTITION BY columns first — allows SQL Server to seek directly to each partition, avoiding a full scan.
• ORDER BY columns next — data arrives pre-sorted, eliminating the sort operator from the plan.
• INCLUDE projected columns — turns the index into a covering index; the query never touches the base table.

Without this index, SQL Server inserts an explicit Sort and Window Spool operator in the execution plan. For large tables (millions of rows), these operators can dominate query cost. Check execution plan warnings for "Sort" operators on window function queries — they are the primary optimisation target.

13. Practical Financial Dashboard: MTD, QTD, YTD, Rolling 30-Day in One SELECT

Combining everything above, here is a production-ready financial dashboard query that computes four KPI periods simultaneously without any joins or CTEs:

SELECT
    order_date,
    region,
    daily_revenue,

    -- Month-to-date
    SUM(daily_revenue)
        OVER (
            PARTITION BY region, YEAR(order_date), MONTH(order_date)
            ORDER BY order_date
            ROWS UNBOUNDED PRECEDING
        ) AS mtd_revenue,

    -- Quarter-to-date
    SUM(daily_revenue)
        OVER (
            PARTITION BY region,
                         YEAR(order_date),
                         DATEPART(QUARTER, order_date)
            ORDER BY order_date
            ROWS UNBOUNDED PRECEDING
        ) AS qtd_revenue,

    -- Year-to-date
    SUM(daily_revenue)
        OVER (
            PARTITION BY region, YEAR(order_date)
            ORDER BY order_date
            ROWS UNBOUNDED PRECEDING
        ) AS ytd_revenue,

    -- Rolling 30-day average
    AVG(daily_revenue * 1.0)
        OVER (
            PARTITION BY region
            ORDER BY order_date
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
        ) AS rolling_30d_avg,

    -- Rolling 30-day total
    SUM(daily_revenue)
        OVER (
            PARTITION BY region
            ORDER BY order_date
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
        ) AS rolling_30d_total,

    -- % of region YTD vs grand total YTD (cross-region share)
    ROUND(
        100.0
        * SUM(daily_revenue) OVER (PARTITION BY region, YEAR(order_date)
                                    ORDER BY order_date ROWS UNBOUNDED PRECEDING)
        / NULLIF(SUM(daily_revenue) OVER (PARTITION BY YEAR(order_date)
                                           ORDER BY order_date ROWS UNBOUNDED PRECEDING), 0),
    2) AS pct_of_total_ytd

FROM regional_daily_sales
ORDER BY region, order_date;

This single query replaces what used to require five separate SQL passes, a stored procedure with temp tables, or complex application-side aggregation. SQL Server's query optimizer processes all window functions in a single scan of the source data (with one sort per distinct window specification), making this pattern both concise and efficient.

CREATE INDEX IX_RegionalSales_Region_Date
    ON regional_daily_sales (region, order_date)
    INCLUDE (daily_revenue);

Summary

• Always specify ROWS UNBOUNDED PRECEDING (not the default RANGE) for running totals to avoid duplicate-date bugs.
• Use ROWS BETWEEN N PRECEDING AND CURRENT ROW for moving averages; cast integers to decimal to avoid truncation.
• Partition by year/month/quarter columns to get YTD/MTD/QTD in a single query without filtering.
• COUNT(*) OVER() with no frame delivers total row counts for pagination without a second query.
• Divide each row's value by SUM(…) OVER(PARTITION BY …) for percentage-of-total without a self-join.
• Design covering indexes with PARTITION BY columns leading, ORDER BY columns next, projected columns in INCLUDE.
• Combine MTD, QTD, YTD, and rolling windows in a single SELECT for concise, efficient analytical dashboards.

Read Next

• SQL Server Window Functions: Complete Guide
• Window Frames in SQL Server: ROWS vs RANGE Explained