Ordinary aggregation collapses rows into groups, while a window function calculates across related rows and keeps the individual result rows.
A total per customer and a customer total displayed beside every sale use the same underlying numbers but need different result shapes. Choosing between GROUP BY and OVER is primarily a decision about which rows the report must retain.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How Aggregate vs window functions works
An ordinary SUM(amount) with GROUP BY customer_id produces one row per customer link. The original sale ids and dates are no longer individual output rows. This shape is suitable for a summary report such as total spending per customer. Every selected expression must fit that grouping grain, either as a grouped expression or as an aggregate. Selecting a sale id beside the grouped total would ask for an individual detail that the group no longer uniquely identifies.
SUM(amount) OVER (PARTITION BY customer_id) instead calculates the total for the partition and repeats it beside each contributing sale row. Ana’s sales 101 and 102 both show forty while retaining their different amounts and dates. This repeated total is useful context, but adding it again across the two result rows would double count Ana’s spending. Window outputs remain detail-level rows even when the calculated column is a summary measurement.
How ORDER BY changes a window total
An OVER clause without ORDER BY defines a partition-wide calculation for SUM. Adding ORDER BY can change the default frame into a running calculation, so it is not just decorative sorting. To request the same entire-partition total when a window order is present, specify a full frame where appropriate. Final ORDER BY controls how the output is displayed; the window’s ordering controls its computation and does not promise final row order.
Window functions generally operate after WHERE and grouping, so they see the rows that survive those earlier operations. You cannot normally use a window result directly in WHERE in the same query level. Put it in a CTE or derived table and filter in an outer SELECT. Advanced queries can aggregate first and then apply a window across the grouped rows. Read each stage’s grain separately rather than assuming every SUM always has the same input shape.
MySQL, PostgreSQL, SQL Server and SQLite
Modern PostgreSQL, MySQL 8+, SQL Server and SQLite 3.25+ support window functions, with feature differences. The shown aggregate window is widely supported. Do not assume QUALIFY exists in these engines as a shared filter syntax; an outer query is the portable approach for filtering calculated window results.
Check the engine-specific syntax and version requirements in PostgreSQL window tutorial.
Practice task and worked solution
Produce a one-row summary for customer 1 rather than retaining both sale rows.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT customer_id,SUM(amount) AS customer_total
FROM sales WHERE customer_id=1 GROUP BY customer_id;customer_id | customer_total
1 | 40The WHERE clause supplies Ana’s two sales. GROUP BY collects them into one customer group and SUM calculates forty. There is one row rather than two, demonstrating the result-shape difference while preserving the same arithmetic total.
Remove PARTITION BY from the window and predict a total over the surviving input rows rather than each customer. Then remove the customer filter and observe eighty-five repeated across all sales. Restore partitioning and compare the two Ana detail rows with the one-row GROUP BY solution. Add a window order only after stating whether you now intend a running or full-partition total.
Aggregate vs Window Functions in SQL: Rows and Groups Syntax
Aggregate vs window functions
SELECT id,customer_id,amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM sales WHERE customer_id=1 ORDER BY id;Aggregate vs Window Functions in SQL: Rows and Groups Summary
- An aggregate with
GROUP BYcollapses each group into one result row. - The same function with
OVER (PARTITION BY ...)keeps every row and adds the group's value beside it. ORDER BYinsideOVERcan turn a total into a running total by changing the window frame.- Window results can't be used in
WHERE; compute them in a subquery or CTE and filter outside. - Choose the shape the consumer needs first: one row per group, or detail rows with group context.
Aggregate vs Window Functions in SQL: Rows and Groups Examples
Each example shows the code, the output it produces and what each part does.
Each sale beside its customer total
Both Ana sales remain visible. The partition is customer 1, and its known amounts sum to forty. The calculation repeats that contextual total rather than reducing the two sales to a single row.
SELECT id,customer_id,amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM sales WHERE customer_id=1 ORDER BY id;id | customer_id | amount | customer_total
101 | 1 | 10 | 40
102 | 1 | 30 | 40Common Aggregate vs Window Functions in SQL: Rows and Groups Mistakes
Summing a repeated window total
Summing customer_total after the detail query double counts each repeated total. If the next report requires one customer total, retain or rebuild one row per customer. Also avoid adding window ORDER BY to a partition-wide total without considering how its frame affects the result.
Aggregate vs Window Functions in SQL: Rows and Groups in Real Projects
Detail rows with context in invoice reviews
An invoice review can display each sale amount with the customer’s total spending for context. The detail table needs the window version; a customer-level export needs ordinary aggregation. Decide which shape the consumer expects before choosing the expression, especially if another calculation will reuse the output.
Record the grain of each intermediate report. A window output with two Ana rows contains the same customer total twice as contextual information. A downstream customer-summary calculation must not add both copies. Keep sale-level and customer-level datasets clearly named so a useful window annotation does not become an inflated aggregate later.
Aggregate vs Window Functions in SQL: Rows and Groups FAQ
Is a window function always an aggregate?
No. Aggregates such as SUM and AVG can be used as window functions, but ROW_NUMBER, RANK, LAG, LEAD and NTILE exist only as window functions and have no GROUP BY form.
Does PARTITION BY remove rows?
No. It defines groups for a calculation while retaining the result rows, unlike GROUP BY, which returns one row per group. Without PARTITION BY, the whole result is one window.
Does window ORDER BY sort the final table?
No. Use an outer ORDER BY for display order; the window order only determines its computation, such as the order of a running total. The final rows may come back in any order without the outer clause.
Can I use GROUP BY and a window function in the same query?
Yes. The window function runs after grouping, over the grouped rows. For example, SUM(SUM(amount)) OVER () next to GROUP BY customer_id shows each customer's total and the grand total, which lets you calculate each customer's share.
How do I filter on a window function result?
Wrap the query in a subquery or CTE and filter in the outer query: WITH ranked AS (SELECT ..., ROW_NUMBER() OVER (...) AS rn FROM sales) SELECT * FROM ranked WHERE rn = 1;. Window functions are calculated after WHERE, so they can't appear there directly.
Aggregate vs Window Functions in SQL: Rows and Groups Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
How many rows remain in the window-total example for customer 1?
Aggregate vs Window Functions in SQL: Rows and Groups Quiz
2 questions to check what you learned.
Which form keeps individual sale rows?
- SUM(amount) with GROUP BY customer_id
- A scalar SUM(amount) with no grouping
- SUM(amount) OVER (PARTITION BY customer_id)
Why is re-summing a repeated window total dangerous?
- The same partition total can appear on multiple detail rows
- Window totals always ignore repeated input records automatically
- A repeated total is guaranteed to describe a different group on every row