SQL SUM(), AVG(), COUNT() as Window Functions

You already know SUM(), AVG(), and COUNT() as aggregate functions that collapse rows into a single result. But when you pair them with the OVER() clause, they become window functions capable of computing running totals, moving averages, and cumulative counts while keeping every single row in your result set intact.

This guide shows you how to use these familiar aggregate functions in a completely new way. You will learn how an SQL running total works under the hood, how to calculate a moving average over a specific number of rows, and how cumulative counts can answer questions that would otherwise require complex subqueries. Every concept is demonstrated with practical examples and their outputs.

Prerequisites

You should be comfortable with basic SQL window function syntax, including the OVER() clause and PARTITION BY. Familiarity with ORDER BY inside OVER() is also helpful. If any of these are new, review an introductory guide to SQL window functions first.

The Sample Data

We will use two tables throughout this guide.

daily_sales table:

CREATE TABLE daily_sales (
    id INT PRIMARY KEY,
    salesperson VARCHAR(50) NOT NULL,
    region VARCHAR(50) NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);
INSERT INTO daily_sales (id, salesperson, region, sale_date, amount) VALUES
(1,  'Alice', 'North', '2024-01-02', 200),
(2,  'Alice', 'North', '2024-01-05', 350),
(3,  'Alice', 'North', '2024-01-09', 150),
(4,  'Alice', 'North', '2024-01-14', 400),
(5,  'Alice', 'North', '2024-01-20', 250),
(6,  'Bob',   'South', '2024-01-03', 300),
(7,  'Bob',   'South', '2024-01-08', 180),
(8,  'Bob',   'South', '2024-01-12', 420),
(9,  'Bob',   'South', '2024-01-17', 270),
(10, 'Bob',   'South', '2024-01-23', 310);

website_events table:

CREATE TABLE website_events (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    event_date DATE NOT NULL
);
INSERT INTO website_events (id, user_id, event_type, event_date) VALUES
(1, 101, 'page_view', '2024-01-01'),
(2, 102, 'signup',    '2024-01-01'),
(3, 101, 'page_view', '2024-01-02'),
(4, 103, 'page_view', '2024-01-02'),
(5, 101, 'purchase',  '2024-01-03'),
(6, 102, 'page_view', '2024-01-03'),
(7, 104, 'signup',    '2024-01-03'),
(8, 103, 'purchase',  '2024-01-04'),
(9, 102, 'purchase',  '2024-01-04'),
(10,104, 'page_view', '2024-01-05');

How ORDER BY Inside OVER() Changes Everything

Before diving into each function, there is one fundamental concept you must understand. When you use an aggregate function as a window function, the presence or absence of ORDER BY inside OVER() completely changes the result.

The reason is the default window frame. When ORDER BY is present, the database implicitly applies ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, meaning the function only considers rows from the start of the partition up to the current row. When ORDER BY is absent, the frame spans the entire partition.

SUM() as a Window Function

Grand Total on Every Row

The simplest use of SUM() OVER() adds a column showing the total across all rows without grouping anything:

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER() AS grand_total
FROM daily_sales;

Output:

Every row sees the same grand_total of 2830. This is useful when you want to calculate each row's percentage of the total:

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER() AS grand_total,
    ROUND(100.0 * amount / SUM(amount) OVER(), 2) AS pct_of_total
FROM daily_sales;

| salesperson | sale_date | amount | grand_total | pct_of_total | |-------------+------------+--------+-------------+--------------| | Alice | 2024-01-02 | 200.00 | 2830.00 | 7.07 | | Alice | 2024-01-05 | 350.00 | 2830.00 | 12.37 | | Alice | 2024-01-09 | 150.00 | 2830.00 | 5.30 | | Alice | 2024-01-14 | 400.00 | 2830.00 | 14.13 | | Alice | 2024-01-20 | 250.00 | 2830.00 | 8.83 | | Bob | 2024-01-03 | 300.00 | 2830.00 | 10.60 | | Bob | 2024-01-08 | 180.00 | 2830.00 | 6.36 | | Bob | 2024-01-12 | 420.00 | 2830.00 | 14.84 | | Bob | 2024-01-17 | 270.00 | 2830.00 | 9.54 | | Bob | 2024-01-23 | 310.00 | 2830.00 | 10.95 |

SQL Running Total

An SQL running total (also called a cumulative sum) adds up values progressively as you move through ordered rows. This is one of the most common analytical calculations in reporting and data analysis.

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER(ORDER BY sale_date) AS running_total
FROM daily_sales;

The running_total starts at 200 and grows with each row. By the last row, it equals the grand total (2830). Notice how the rows are sorted by sale_date because that is the ORDER BY we specified inside OVER().

Running Total Per Partition

In practice, you almost always want a running total within a group, such as per salesperson, per department, or per region. Add PARTITION BY to reset the running total for each group:

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER(
        PARTITION BY salesperson
        ORDER BY sale_date
    ) AS running_total
FROM daily_sales;

Output:

Alice's running total resets and accumulates independently from Bob's. Alice's final running total is 1350 and Bob's is 1480, which together equal the grand total of 2830.

Running Total with Percentage Progress

Combine the partitioned running total with the partition total to show progress as a percentage:

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER(
        PARTITION BY salesperson
        ORDER BY sale_date
    ) AS running_total,
    SUM(amount) OVER(
        PARTITION BY salesperson
    ) AS person_total,
    ROUND(
        100.0 * SUM(amount) OVER(PARTITION BY salesperson ORDER BY sale_date)
             / SUM(amount) OVER(PARTITION BY salesperson),
        1
    ) AS pct_progress
FROM daily_sales;

Output:

Notice how the same query uses two SUM() OVER() calls with different OVER() definitions. One with ORDER BY (running total) and one without (full partition total). This is a very common and powerful pattern.

AVG() as a Window Function

Cumulative Average

Just like SUM(), using AVG() with ORDER BY inside OVER() produces a cumulative (running) average rather than the overall average:

SELECT
    salesperson,
    sale_date,
    amount,
    ROUND(AVG(amount) OVER(
        PARTITION BY salesperson
        ORDER BY sale_date
    ), 2) AS running_avg
FROM daily_sales;

For Alice, the running average is calculated as:

• Row 1: 200 / 1 = 200.00
• Row 2: (200 + 350) / 2 = 275.00
• Row 3: (200 + 350 + 150) / 3 = 233.33
• Row 4: (200 + 350 + 150 + 400) / 4 = 275.00
• Row 5: (200 + 350 + 150 + 400 + 250) / 5 = 270.00

Each row's average includes all rows from the start of the partition up to and including the current row.

Moving Average with a Fixed Window Frame

A moving average looks at only a fixed number of recent rows, not all previous rows. This is extremely common in financial analysis, time series reporting, and dashboards. To achieve this, you need to explicitly define the window frame using ROWS BETWEEN.

3-Row Moving Average

SELECT
    salesperson,
    sale_date,
    amount,
    ROUND(AVG(amount) OVER(
        PARTITION BY salesperson
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3
FROM daily_sales;

Let's trace through Alice's calculations:

• Row 1: only 1 row available, so avg = 200 / 1 = 200.00
• Row 2: 2 rows available, avg = (200 + 350) / 2 = 275.00
• Row 3: 3 rows available, avg = (200 + 350 + 150) / 3 = 233.33
• Row 4: window slides, avg = (350 + 150 + 400) / 3 = 300.00
• Row 5: window slides, avg = (150 + 400 + 250) / 3 = 266.67

Starting from row 3, the window always includes exactly 3 rows: the current row plus the 2 preceding ones. Earlier rows "fall off" the left side of the window.

Understanding ROWS BETWEEN Syntax

The ROWS BETWEEN clause defines the window frame, that is, exactly which rows the function should consider relative to the current row.

ROWS BETWEEN <start> AND <end>

Common frame boundaries:

Some practical combinations:

-- Last 5 rows including current (5-row moving window)
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW

-- Current row and the next 2 rows (forward-looking)
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING

-- Entire partition (explicit version of default without ORDER BY)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- Running total (explicit version of default with ORDER BY)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Centered Moving Average

Sometimes you want a window that looks both backward and forward:

SELECT
    salesperson,
    sale_date,
    amount,
    ROUND(AVG(amount) OVER(
        PARTITION BY salesperson
        ORDER BY sale_date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ), 2) AS centered_avg
FROM daily_sales;

For Alice's row 3 (amount 150): the window includes row 2 (350), row 3 (150), and row 4 (400). Average = (350 + 150 + 400) / 3 = 300.00. The first and last rows of each partition have smaller windows because there is no preceding or following row available.

COUNT() as a Window Function

Cumulative Count

A cumulative count tracks how many rows have been seen so far as you move through the ordered data:

SELECT
    salesperson,
    sale_date,
    amount,
    COUNT(*) OVER(
        PARTITION BY salesperson
        ORDER BY sale_date
    ) AS cumulative_count
FROM daily_sales;

This looks similar to ROW_NUMBER(), but there is a critical difference when ties exist. COUNT() with ORDER BY will assign the same cumulative count to rows with identical ORDER BY values, while ROW_NUMBER() always produces unique numbers. We will demonstrate this difference shortly.

Cumulative Count of Distinct Event Types

Using the website_events table, let's count how many events have occurred cumulatively per day:

SELECT
    event_date,
    event_type,
    user_id,
    COUNT(*) OVER(ORDER BY event_date) AS cumulative_events
FROM website_events;

Notice that on 2024-01-01, both rows show cumulative_events = 2 (not 1 and 2). This happens because both rows share the same event_date value in the ORDER BY, and the default frame with ORDER BY treats rows with equal sort values as peers. All peers are included together.

Partition Count on Every Row

Without ORDER BY, COUNT() gives the total count for the entire partition, repeated on every row:

SELECT
    salesperson,
    sale_date,
    amount,
    COUNT(*) OVER(PARTITION BY salesperson) AS total_sales_count
FROM daily_sales;

Output:

This is useful for calculating things like "what fraction of this person's total sales does this individual sale represent?"

Combining Multiple Window Functions in One Query

One of the biggest strengths of window functions is that you can use several of them in the same SELECT, each with its own OVER() definition:

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER(
        PARTITION BY salesperson ORDER BY sale_date
    ) AS running_total,
    ROUND(AVG(amount) OVER(
        PARTITION BY salesperson ORDER BY sale_date
    ), 2) AS running_avg,
    COUNT(*) OVER(
        PARTITION BY salesperson ORDER BY sale_date
    ) AS running_count,
    SUM(amount) OVER(
        PARTITION BY salesperson
    ) AS person_total,
    ROUND(AVG(amount) OVER(
        PARTITION BY salesperson ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3
FROM daily_sales;

Output:

This single query gives you five different analytical perspectives on the same data. Achieving this without window functions would require multiple subqueries or self-joins.

Real-World Example: Daily Signup Tracking

Let's use the website_events table to build a daily signup report with cumulative counts:

WITH daily_signups AS (
    SELECT
        event_date,
        COUNT(*) AS signups_today
    FROM website_events
    WHERE event_type = 'signup'
    GROUP BY event_date
)
SELECT
    event_date,
    signups_today,
    SUM(signups_today) OVER(ORDER BY event_date) AS total_signups,
    ROUND(AVG(signups_today) OVER(ORDER BY event_date), 2) AS avg_daily_signups
FROM daily_signups;

Output:

This pattern is extremely common in dashboards. First aggregate to the grain you need (daily in this case), then apply window functions for cumulative metrics.

Common Mistakes to Avoid

Mistake 1: Expecting a Running Total Without ORDER BY

-- WRONG: This gives the total for the entire partition, not a running total
SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER(PARTITION BY salesperson) AS not_running
FROM daily_sales;

Every row for Alice shows 1350. Every row for Bob shows 1480. No accumulation.

Fix: Add ORDER BY inside OVER():

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER(PARTITION BY salesperson ORDER BY sale_date) AS running_total
FROM daily_sales;

Mistake 2: Using ROWS When You Mean RANGE (or Vice Versa)

SQL supports two types of frame specification: ROWS and RANGE. They behave differently when there are tied values in the ORDER BY column.

• ROWS counts physical rows regardless of value.
• RANGE includes all rows with the same value as the boundary row.

The default when you use ORDER BY without specifying ROWS or RANGE is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in most databases. This means all rows with the same ORDER BY value are treated as peers and included together.

-- Default behavior (RANGE): peers are included together
SELECT
    event_date,
    event_type,
    COUNT(*) OVER(ORDER BY event_date) AS cumulative_count
FROM website_events;

On 2024-01-01, both rows show cumulative_count = 2 because both share the same date.

If you want strictly row-by-row accumulation even with ties, use ROWS explicitly:

SELECT
    event_date,
    event_type,
    COUNT(*) OVER(
        ORDER BY event_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS strict_cumulative_count
FROM website_events;

Output:

Now each row gets a strictly incrementing count.

Mistake 3: Filtering on Window Function Results in WHERE

Window functions are evaluated after WHERE. You cannot reference them in WHERE:

-- WRONG: Will fail
SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER(ORDER BY sale_date) AS running_total
FROM daily_sales
WHERE running_total > 1000;

Error: column "running_total" does not exist (or similar)

Fix: Use a subquery or CTE:

WITH sales_with_running AS (
    SELECT
        salesperson,
        sale_date,
        amount,
        SUM(amount) OVER(ORDER BY sale_date) AS running_total
    FROM daily_sales
)
SELECT *
FROM sales_with_running
WHERE running_total > 1000;

Mistake 4: Mixing GROUP BY and Window Functions Incorrectly

When a query has both GROUP BY and window functions, the window function operates on the already-grouped result, not the original rows. This is correct behavior, but can be surprising:

SELECT
    salesperson,
    SUM(amount) AS total_sales,
    SUM(SUM(amount)) OVER() AS company_total
FROM daily_sales
GROUP BY salesperson;

The inner SUM(amount) is the aggregate for GROUP BY. The outer SUM(...) OVER() is the window function that sums those aggregated values. The nesting SUM(SUM(amount)) looks unusual but is correct and necessary.

Performance Tips

• Index the ORDER BY column. Running totals and moving averages require sorted data. An index on the ORDER BY column (or a composite index including PARTITION BY columns) helps the database avoid expensive sort operations.

• Minimize the number of distinct OVER() definitions. Queries with many different window definitions may require multiple sort passes over the data. Where possible, reuse the same OVER() clause or use named windows.

• Prefer window functions over self-joins. Before window functions existed, running totals required correlated subqueries or self-joins, which scale poorly. A single SUM() OVER() is almost always faster and more readable.

• Be mindful of frame size. Large frames (like ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW) require the database to look at many rows for each output row. This is usually fine but worth noting for very large datasets.

Summary

SUM(), AVG(), and COUNT() are not just aggregate functions. When combined with the OVER() clause, they become powerful analytical tools:

• SUM() OVER(ORDER BY ...) produces an SQL running total that accumulates as you move through ordered rows.
• AVG() OVER(ROWS BETWEEN N PRECEDING AND CURRENT ROW) calculates a moving average over a fixed number of recent rows.
• COUNT() OVER(ORDER BY ...) generates a cumulative count that tracks how many rows have been processed so far.
• Without ORDER BY, all three functions return the full partition aggregate on every row.
• PARTITION BY resets the calculation for each group independently.
• The window frame (ROWS BETWEEN ... AND ...) gives you precise control over which rows the function considers.

These patterns are the backbone of time-series analysis, financial reporting, dashboards, and KPI tracking. Once you internalize how ORDER BY inside OVER() changes behavior from "total" to "running," these functions become tools you will reach for daily.