Aggregating Across Groups with SQL Window Functions: SUM, AVG, and COUNT OVER PARTITION BY

Introduction

Imagine you're a data analyst at a retail company, and your manager asks you a deceptively simple question: "Show me each sale along with the total revenue for that product category." You pull up your SQL editor, write a quick GROUP BY query — and immediately hit a wall. A standard aggregation collapses your rows into groups, so you can't see individual sales and category totals at the same time. You'd have to write a subquery, or a JOIN, or some other workaround that makes the query feel like it's fighting you.

Window functions are the solution your manager's question was quietly demanding. They let you perform aggregations — sums, averages, counts — across defined groups of rows, while keeping every individual row intact in your result set. The row doesn't get swallowed up by the group; it stays visible, now carrying the group-level calculation alongside it. This single capability unlocks an entire class of analytical queries that are otherwise awkward or impossible to write cleanly.

By the end of this lesson, you'll be able to write SUM, AVG, and COUNT window functions using OVER (PARTITION BY ...) syntax, understand the conceptual difference between window aggregation and GROUP BY aggregation, and apply these tools to realistic business scenarios. You'll also understand the most common mistakes people make when learning window functions so you can avoid them.

What you'll learn:

• What a window function is and how it differs from a standard GROUP BY aggregation
• How to use PARTITION BY to define groups for a window calculation
• How to apply SUM, AVG, and COUNT as window functions
• How to combine window aggregates with regular columns in a single query
• How to troubleshoot common errors and misunderstandings

---

Prerequisites

This lesson assumes you're comfortable with:

• Basic SELECT statements with WHERE and ORDER BY
• Standard aggregate functions like SUM(), AVG(), and COUNT() used with GROUP BY
• Basic understanding of what a subquery is (you don't need to write them, just know the concept exists)

If GROUP BY feels shaky, spend 20 minutes reviewing it first — the contrast between GROUP BY and PARTITION BY is the conceptual heart of this lesson.

---

The Problem With GROUP BY Alone

Let's set up a realistic scenario we'll use throughout this lesson. You work with a table called sales at a retail company. Here's what it looks like:

-- The sales table
-- sale_id | rep_name    | region    | amount
-- --------|-------------|-----------|--------
-- 1       | Alice       | Northeast | 4200
-- 2       | Bob         | Northeast | 3100
-- 3       | Carol       | Southeast | 5800
-- 4       | Dave        | Southeast | 2900
-- 5       | Eve         | Northeast | 6100
-- 6       | Frank       | Southeast | 4400

Your manager asks: "Show me each salesperson's individual sale, along with the total sales for their region."

Your first instinct might be a GROUP BY query:

SELECT
    region,
    SUM(amount) AS regional_total
FROM sales
GROUP BY region;

This gives you:

region    | regional_total
----------|---------------
Northeast | 13400
Southeast | 13100

Useful, but wrong for the task. You've lost the individual rows — Alice, Bob, Eve, Carol, Dave, and Frank are all gone. You have region totals but no salesperson detail. To get both at once, you'd traditionally need something like a self-join or a correlated subquery, which gets messy fast.

This is exactly the problem window functions were designed to solve.

---

What Is a Window Function?

A window function performs a calculation across a set of rows that are related to the current row. That set of rows is called the window — think of it as a sliding frame or lens that the function looks through when computing its result.

The crucial difference from GROUP BY:

• GROUP BY collapses multiple rows into a single output row per group.
• A window function computes a result for each row based on a group of related rows, but leaves all the original rows intact.

The window is defined using an OVER clause. The OVER clause is what transforms a regular aggregate function into a window function. Without OVER, SUM(amount) is a standard aggregation. With OVER, it becomes a window function.

Here's the basic syntax:

function_name(column) OVER (PARTITION BY grouping_column)

• function_name — the aggregate you want to compute (SUM, AVG, COUNT, etc.)
• column — the column to aggregate
• OVER — the keyword that signals "this is a window function"
• PARTITION BY grouping_column — this defines the groups (the "window") for the calculation

Think of PARTITION BY as the window function's version of GROUP BY. It divides the rows into groups, and the function is computed separately within each group.

---

Your First Window Function: SUM OVER PARTITION BY

Let's solve the original problem. We want each sale row, plus the regional total:

SELECT
    sale_id,
    rep_name,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS regional_total
FROM sales;

Result:

sale_id | rep_name | region    | amount | regional_total
--------|----------|-----------|--------|---------------
1       | Alice    | Northeast | 4200   | 13400
2       | Bob      | Northeast | 3100   | 13400
5       | Eve      | Northeast | 6100   | 13400
3       | Carol    | Southeast | 5800   | 13100
4       | Dave     | Southeast | 2900   | 13100
6       | Frank    | Southeast | 4400   | 13100

Every row is still there. Alice, Bob, and Eve all show 13400 — the total for Northeast. Carol, Dave, and Frank all show 13100 — the total for Southeast. The individual amount column and the regional_total column coexist peacefully in the same row.

Let's trace through what SQL is doing here, step by step:

1. SQL fetches all rows from sales.
2. For each row, it looks at the PARTITION BY region clause and identifies which group (Northeast or Southeast) that row belongs to.
3. It computes SUM(amount) for all rows in that same group.
4. It writes that sum into the regional_total column for the current row.
5. It moves to the next row and repeats.

Notice that the output is still six rows — the same number as the input. Nothing got collapsed.

Tip: The order of rows in the output may vary depending on your database. If you want a specific order, add an ORDER BY clause at the end of your query (outside the OVER clause). For example: ORDER BY region, sale_id.

---

Partitioning by Multiple Columns

PARTITION BY isn't limited to a single column. You can partition by two or more columns to define more granular groups.

Let's extend our table slightly. Suppose sales also has a quarter column:

SELECT
    rep_name,
    region,
    quarter,
    amount,
    SUM(amount) OVER (PARTITION BY region, quarter) AS region_quarter_total
FROM sales;

This computes the total sales for each combination of region and quarter. A Northeast salesperson in Q1 would see the total for all Northeast Q1 sales in their region_quarter_total column — not the total for all Northeast sales across all quarters.

The rule is simple: you list all the columns you want to group by inside PARTITION BY, separated by commas, exactly like you would with GROUP BY.

---

Omitting PARTITION BY: A Grand Total Window

What happens if you write OVER () with nothing inside it?

SELECT
    sale_id,
    rep_name,
    region,
    amount,
    SUM(amount) OVER () AS grand_total
FROM sales;

Result:

sale_id | rep_name | region    | amount | grand_total
--------|----------|-----------|--------|------------
1       | Alice    | Northeast | 4200   | 26500
2       | Bob      | Northeast | 3100   | 26500
3       | Carol    | Southeast | 5800   | 26500
4       | Dave     | Southeast | 2900   | 26500
5       | Eve      | Northeast | 6100   | 26500
6       | Frank    | Southeast | 4400   | 26500

With no PARTITION BY, the entire result set is treated as a single window. Every row gets the same grand total. This is genuinely useful when you want to calculate something like each row's percentage of the overall total, which we'll do shortly.

---

AVG OVER PARTITION BY: Group Averages Alongside Individual Values

AVG works the same way. Suppose you want to see each salesperson's amount alongside the average for their region, so you can quickly spot who's above or below average:

SELECT
    rep_name,
    region,
    amount,
    ROUND(AVG(amount) OVER (PARTITION BY region), 2) AS regional_avg
FROM sales
ORDER BY region, amount DESC;

Result:

rep_name | region    | amount | regional_avg
---------|-----------|--------|-------------
Eve      | Northeast | 6100   | 4466.67
Alice    | Northeast | 4200   | 4466.67
Bob      | Northeast | 3100   | 4466.67
Carol    | Southeast | 5800   | 4366.67
Frank    | Southeast | 4400   | 4366.67
Dave     | Southeast | 2900   | 4366.67

Now you can see at a glance that Eve is well above the Northeast average, while Bob is dragging it down. Dave is significantly below the Southeast average. This kind of comparison — individual value vs. group benchmark — is one of the most common uses of window functions in real analytical work.

Tip: ROUND(value, 2) just rounds the result to 2 decimal places. You can wrap any window function result in ROUND() for cleaner output.

---

COUNT OVER PARTITION BY: How Many Rows Are in Each Group?

COUNT as a window function tells you how many rows exist in each partition, attached to every row in that partition. This is useful when you need context about group size without losing row-level detail.

SELECT
    rep_name,
    region,
    amount,
    COUNT(*) OVER (PARTITION BY region) AS reps_in_region
FROM sales
ORDER BY region;

Result:

rep_name | region    | amount | reps_in_region
---------|-----------|--------|---------------
Alice    | Northeast | 4200   | 3
Bob      | Northeast | 3100   | 3
Eve      | Northeast | 6100   | 3
Carol    | Southeast | 5800   | 3
Dave     | Southeast | 2900   | 3
Frank    | Southeast | 4400   | 3

Each row now knows how many total salespeople exist in its region. This is particularly powerful when you later want to calculate each rep's contribution to their region (amount / regional total) or when you need to join this data with other tables and want to carry the group size along.

Note: COUNT(*) counts all rows in the partition. COUNT(column_name) counts only non-NULL values in that column within the partition. This behaves the same as standard COUNT — the window just defines which rows to count.

---

Putting It All Together: A Realistic Analytical Query

Here's where window functions really shine — combining multiple window calculations in a single query to create a comprehensive analytical view:

SELECT
    rep_name,
    region,
    amount,
    SUM(amount)   OVER (PARTITION BY region) AS regional_total,
    ROUND(AVG(amount) OVER (PARTITION BY region), 2) AS regional_avg,
    COUNT(*)      OVER (PARTITION BY region) AS reps_in_region,
    SUM(amount)   OVER ()                   AS grand_total,
    ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM sales
ORDER BY region, amount DESC;

Result:

rep_name | region    | amount | regional_total | regional_avg | reps_in_region | grand_total | pct_of_region
---------|-----------|--------|----------------|--------------|----------------|-------------|---------------
Eve      | Northeast | 6100   | 13400          | 4466.67      | 3              | 26500       | 45.5
Alice    | Northeast | 4200   | 13400          | 4466.67      | 3              | 26500       | 31.3
Bob      | Northeast | 3100   | 13400          | 4466.67      | 3              | 26500       | 23.1
Carol    | Southeast | 5800   | 13100          | 4366.67      | 3              | 26500       | 44.3
Frank    | Southeast | 4400   | 13100          | 4366.67      | 3              | 26500       | 33.6
Dave     | Southeast | 2900   | 13100          | 4366.67      | 3              | 26500       | 22.1

One query. Six rows in, six rows out. Every piece of context you could want for a performance dashboard: individual amounts, regional totals, regional averages, team size, company-wide totals, and each rep's share of their region. This would have required multiple subqueries or CTEs to replicate with GROUP BY alone.

The pct_of_region column shows the calculation trick: 100.0 * amount / SUM(amount) OVER (PARTITION BY region). You're dividing the current row's amount by the window-computed regional total — a calculation that's natural to express with window functions and awkward without them.

---

Hands-On Exercise

Create the following table and data in your SQL environment (PostgreSQL, MySQL 8+, SQLite 3.25+, or SQL Server all support this syntax):

CREATE TABLE orders (
    order_id    INT,
    customer    VARCHAR(50),
    category    VARCHAR(50),
    order_total DECIMAL(10,2)
);

INSERT INTO orders VALUES
(1,  'Nguyen',    'Electronics', 899.00),
(2,  'Patel',     'Clothing',    145.00),
(3,  'Williams',  'Electronics', 1249.00),
(4,  'Okafor',    'Clothing',    210.00),
(5,  'Tanaka',    'Electronics', 560.00),
(6,  'Rossi',     'Furniture',   1800.00),
(7,  'Chen',      'Furniture',   950.00),
(8,  'Alvarez',   'Clothing',    88.00);

Exercise 1: Write a query that shows every order alongside the total revenue for its category.

Exercise 2: Add a column showing the average order value for each category, rounded to 2 decimal places.

Exercise 3: Add a column showing how many orders exist in each category.

Exercise 4: Add a column showing each order's percentage of its category's total revenue (as a decimal or percentage — your choice).

Challenge: Rewrite Exercise 4 using a GROUP BY query instead of a window function. Notice how much more complex it becomes, and how the output differs in structure. This contrast is the lesson.

Expected output for Exercises 1–3 (partial):

order_id | customer | category    | order_total | cat_total | cat_avg  | cat_count
---------|----------|-------------|-------------|-----------|----------|----------
1        | Nguyen   | Electronics | 899.00      | 2708.00   | 902.67   | 3
3        | Williams | Electronics | 1249.00     | 2708.00   | 902.67   | 3
...

---

Common Mistakes & Troubleshooting

Mistake 1: Trying to Filter on a Window Function in the WHERE Clause

This is one of the most common errors beginners hit:

-- THIS WILL FAIL
SELECT
    rep_name,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS regional_total
FROM sales
WHERE regional_total > 13000; -- ERROR: column doesn't exist yet

Window functions are computed after the WHERE clause runs. The column regional_total doesn't exist at the point WHERE is evaluated. To filter on a window function result, wrap your query in a CTE or subquery:

WITH sales_with_totals AS (
    SELECT
        rep_name,
        region,
        amount,
        SUM(amount) OVER (PARTITION BY region) AS regional_total
    FROM sales
)
SELECT *
FROM sales_with_totals
WHERE regional_total > 13000;

Mistake 2: Confusing PARTITION BY and ORDER BY Inside OVER

PARTITION BY divides rows into groups. ORDER BY inside OVER is used for ordered window functions like running totals or rankings — it does not sort your final output. If you see unexpected results from a SUM() OVER (PARTITION BY x ORDER BY y), know that adding ORDER BY inside OVER changes the behavior from a full-group sum to a cumulative sum. That's a different topic, and unless you specifically want a running total, leave ORDER BY out of your OVER clause for simple aggregations.

Mistake 3: Using Window Functions in GROUP BY or HAVING Clauses

Window functions cannot appear in GROUP BY or HAVING. They also can't appear in WHERE. The only places you can use them are SELECT and ORDER BY (in most databases). If you need to filter or group by a window result, use a CTE or subquery, as shown above.

Mistake 4: Forgetting That Window Functions Don't Change Row Count

If you're expecting fewer rows after using a window function, you've confused it with GROUP BY. Window functions never reduce your row count. If you want grouped output and window calculations, you may need both in a single pipeline — use the window function in a CTE first, then apply GROUP BY to the CTE result.

Mistake 5: Database Compatibility

Window functions using OVER (PARTITION BY ...) are available in:

• PostgreSQL (all modern versions)
• MySQL 8.0 and later
• SQLite 3.25.0 and later
• SQL Server 2005 and later
• BigQuery, Snowflake, Redshift, DuckDB

If you're on MySQL 5.x, window functions are not supported. Upgrade or use a subquery workaround.

---

Summary & Next Steps

Window functions with OVER (PARTITION BY ...) solve a genuine gap in SQL's expressive power. They let you compute group-level aggregations — sums, averages, counts — while keeping every individual row visible in your output. The PARTITION BY clause is the key: it defines the group boundaries without collapsing the rows the way GROUP BY does.

Here's what you can now do:

• SUM(amount) OVER (PARTITION BY region) — total for the group, on every row
• AVG(amount) OVER (PARTITION BY region) — group average, on every row
• COUNT(*) OVER (PARTITION BY region) — group row count, on every row
• OVER () with no partition — whole-table aggregate on every row
• Multiple window functions in one query — build rich analytical views without subqueries

Where to go next:

1. Running Totals and Cumulative Sums — Learn how adding ORDER BY inside OVER changes a full-group SUM into a running total, which is invaluable for tracking cumulative revenue or progress over time.
2. Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK — Window functions that assign ranks within partitions, essential for "top N per group" queries.
3. LAG and LEAD — Window functions that let you look at the previous or next row's value, perfect for period-over-period comparisons.
4. Named Windows with WINDOW Clause — When you have multiple window functions sharing the same OVER definition, the WINDOW clause lets you define it once and reuse it, keeping your queries clean.

Window functions are one of those features that, once you understand them, you'll wonder how you ever got along without them. The patterns you learned here — individual value alongside group aggregate, percentage of group total, group size on every row — appear constantly in analytical SQL. You now have the tools to write them cleanly.