Skip to main content

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:

idsalespersonregionsale_dateamount
1AliceNorth2024-01-02200
2AliceNorth2024-01-05350
3AliceNorth2024-01-09150
4AliceNorth2024-01-14400
5AliceNorth2024-01-20250
6BobSouth2024-01-03300
7BobSouth2024-01-08180
8BobSouth2024-01-12420
9BobSouth2024-01-17270
10BobSouth2024-01-23310
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:

iduser_idevent_typeevent_date
1101page_view2024-01-01
2102signup2024-01-01
3101page_view2024-01-02
4103page_view2024-01-02
5101purchase2024-01-03
6102page_view2024-01-03
7104signup2024-01-03
8103purchase2024-01-04
9102purchase2024-01-04
10104page_view2024-01-05
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.

ClauseBehavior
SUM(amount) OVER()Total across all rows. Same value on every row.
SUM(amount) OVER(ORDER BY sale_date)Running total that accumulates row by row.
SUM(amount) OVER(PARTITION BY region)Total for each partition. Same value for all rows in the same group.
SUM(amount) OVER(PARTITION BY region ORDER BY sale_date)Running total that accumulates row by row within each partition.

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.

info

This default frame behavior applies to SUM(), AVG(), and COUNT() alike. Understanding it is the key to mastering SQL running totals, cumulative counts, and moving averages.

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:

salespersonsale_dateamountgrand_total
Alice2024-01-02200.002830.00
Alice2024-01-05350.002830.00
Alice2024-01-09150.002830.00
Alice2024-01-14400.002830.00
Alice2024-01-20250.002830.00
Bob2024-01-03300.002830.00
Bob2024-01-08180.002830.00
Bob2024-01-12420.002830.00
Bob2024-01-17270.002830.00
Bob2024-01-23310.002830.00

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;
salespersonsale_dateamountrunning_total
Alice2024-01-02200.00200.00
Bob2024-01-03300.00500.00
Alice2024-01-05350.00850.00
Bob2024-01-08180.001030.00
Alice2024-01-09150.001180.00
Bob2024-01-12420.001600.00
Alice2024-01-14400.002000.00
Bob2024-01-17270.002270.00
Alice2024-01-20250.002520.00
Bob2024-01-23310.002830.00

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:

salespersonsale_dateamountrunning_total
Alice2024-01-02200.00200.00
Alice2024-01-05350.00550.00
Alice2024-01-09150.00700.00
Alice2024-01-14400.001100.00
Alice2024-01-20250.001350.00
Bob2024-01-03300.00300.00
Bob2024-01-08180.00480.00
Bob2024-01-12420.00900.00
Bob2024-01-17270.001170.00
Bob2024-01-23310.001480.00

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:

salespersonsale_dateamountrunning_totalperson_totalpct_progress
Alice2024-01-02200.00200.001350.0014.8
Alice2024-01-05350.00550.001350.0040.7
Alice2024-01-09150.00700.001350.0051.9
Alice2024-01-14400.001100.001350.0081.5
Alice2024-01-20250.001350.001350.00100.0
Bob2024-01-03300.00300.001480.0020.3
Bob2024-01-08180.00480.001480.0032.4
Bob2024-01-12420.00900.001480.0060.8
Bob2024-01-17270.001170.001480.0079.1
Bob2024-01-23310.001480.001480.00100.0

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.

Common Mistake: Confusing Partition Total with Running Total

Forgetting ORDER BY inside OVER() when you want a running total is a frequent error.

-- WRONG: This gives the full partition total, not a running total
SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER(PARTITION BY salesperson) AS this_is_NOT_a_running_total
FROM daily_sales;
salespersonsale_dateamountthis_is_not_a_running_total
Alice2024-01-02200.001350.00
Alice2024-01-05350.001350.00
Alice2024-01-09150.001350.00
Alice2024-01-14400.001350.00
Alice2024-01-20250.001350.00
Bob2024-01-03300.001480.00
Bob2024-01-08180.001480.00
Bob2024-01-12420.001480.00
Bob2024-01-17270.001480.00
Bob2024-01-23310.001480.00

Every row for Alice would show 1350, and every row for Bob would show 1480. There is no accumulation. Always include ORDER BY to get an SQL running total.

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;
salespersonsale_dateamountrunning_avg
Alice2024-01-02200.00200.00
Alice2024-01-05350.00275.00
Alice2024-01-09150.00233.33
Alice2024-01-14400.00275.00
Alice2024-01-20250.00270.00
Bob2024-01-03300.00300.00
Bob2024-01-08180.00240.00
Bob2024-01-12420.00300.00
Bob2024-01-17270.00292.50
Bob2024-01-23310.00296.00

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;
salespersonsale_dateamountmoving_avg_3
Alice2024-01-02200.00200.00
Alice2024-01-05350.00275.00
Alice2024-01-09150.00233.33
Alice2024-01-14400.00300.00
Alice2024-01-20250.00266.67
Bob2024-01-03300.00300.00
Bob2024-01-08180.00240.00
Bob2024-01-12420.00300.00
Bob2024-01-17270.00290.00
Bob2024-01-23310.00333.33

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.

tip

Compare moving_avg_3 with the running_avg from the previous example. For rows 1 through 3 the values are identical, because fewer than 3 previous rows exist. From row 4 onward they diverge: the running average keeps growing its denominator while the moving average stays fixed at 3 rows.

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:

BoundaryMeaning
UNBOUNDED PRECEDINGThe first row of the partition
N PRECEDINGN rows before the current row
CURRENT ROWThe current row itself
N FOLLOWINGN rows after the current row
UNBOUNDED FOLLOWINGThe last row of the partition

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;
salespersonsale_dateamountcentered_avg
Alice2024-01-02200.00275.00
Alice2024-01-05350.00233.33
Alice2024-01-09150.00300.00
Alice2024-01-14400.00266.67
Alice2024-01-20250.00325.00
Bob2024-01-03300.00240.00
Bob2024-01-08180.00300.00
Bob2024-01-12420.00290.00
Bob2024-01-17270.00333.33
Bob2024-01-23310.00290.00

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;
salespersonsale_dateamountcumulative_count
Alice2024-01-022001
Alice2024-01-053502
Alice2024-01-091503
Alice2024-01-144004
Alice2024-01-202505
Bob2024-01-033001
Bob2024-01-081802
Bob2024-01-124203
Bob2024-01-172704
Bob2024-01-233105

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;
event_dateevent_typeuser_idcumulative_events
2024-01-01page_view1012
2024-01-01signup1022
2024-01-02page_view1014
2024-01-02page_view1034
2024-01-03purchase1017
2024-01-03page_view1027
2024-01-03signup1047
2024-01-04purchase1039
2024-01-04purchase1029
2024-01-05page_view10410

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.

COUNT(*) vs ROW_NUMBER() with Ties

This behavior differentiates COUNT(*) OVER(ORDER BY ...) from ROW_NUMBER() OVER(ORDER BY ...). ROW_NUMBER() would assign 1 and 2 to those two rows (arbitrarily breaking the tie), while COUNT(*) gives both of them 2 because it includes all rows up to and including the current peer group.

If you want each row to have a strictly incrementing number regardless of ties, use ROW_NUMBER(). If you want the count to reflect how many rows exist up to and including the current sort value, use COUNT(*).

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:

salespersonsale_dateamounttotal_sales_count
Alice2024-01-022005
Alice2024-01-053505
Alice2024-01-091505
Alice2024-01-144005
Alice2024-01-202505
Bob2024-01-033005
Bob2024-01-081805
Bob2024-01-124205
Bob2024-01-172705
Bob2024-01-233105

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:

salespersonsale_dateamountrunning_totalrunning_avgrunning_countperson_totalmoving_avg_3
Alice2024-01-02200.00200.00200.0011350.00200.00
Alice2024-01-05350.00550.00275.0021350.00275.00
Alice2024-01-09150.00700.00233.3331350.00233.33
Alice2024-01-14400.001100.00275.0041350.00300.00
Alice2024-01-20250.001350.00270.0051350.00266.67
Bob2024-01-03300.00300.00300.0011480.00300.00
Bob2024-01-08180.00480.00240.0021480.00240.00
Bob2024-01-12420.00900.00300.0031480.00300.00
Bob2024-01-17270.001170.00292.5041480.00290.00
Bob2024-01-23310.001480.00296.0051480.00333.33

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.

Named Windows for Cleaner Queries

If your database supports it (PostgreSQL, MySQL 8+), you can define a named window to avoid repeating the same OVER() definition:

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount)   OVER w AS running_total,
    ROUND(AVG(amount) OVER w, 2) AS running_avg,
    COUNT(*)      OVER w AS running_count
FROM daily_sales
WINDOW w AS (PARTITION BY salesperson ORDER BY sale_date);

This produces the same result but is much easier to read and maintain.

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:

event_datesignups_todaytotal_signupsavg_daily_signups
2024-01-01111.00
2024-01-03121.00

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:

event_dateevent_typestrict_cumulative_count
2024-01-01page_view1
2024-01-01signup2
2024-01-02page_view3
2024-01-02page_view4
2024-01-03purchase5
2024-01-03page_view6
2024-01-03signup7
2024-01-04purchase8
2024-01-04purchase9
2024-01-05page_view10

Now each row gets a strictly incrementing count.

caution

Be aware that when using ROWS with tied ORDER BY values, the order among the tied rows is non-deterministic unless you add a tiebreaker to ORDER BY. This can result in different running totals across executions for the rows within a tie group, even though the final cumulative value at the end of the tie group remains the same.

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;
salespersonsale_dateamountrunning_total
Bob2024-01-08180.001030.00
Alice2024-01-09150.001180.00
Bob2024-01-12420.001600.00
Alice2024-01-14400.002000.00
Bob2024-01-17270.002270.00
Alice2024-01-20250.002520.00
Bob2024-01-23310.002830.00

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;
salespersontotal_salescompany_total
Alice1350.002830.00
Bob1480.002830.00

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.

Table of Contents