Use an aggregate with GROUP BY when you want a summary row for each group. Use a window function with OVER when you want a calculation—such as a group total, rank, or running total—while keeping the underlying rows in the result. The difference is the result shape: GROUP BY summarizes; a window calculation annotates rows.
What is the difference between aggregate and window functions?
Common aggregate functions include SUM, AVG, COUNT, MIN, and MAX. They calculate a value over a set of values. With GROUP BY, the query divides input rows into groups and returns a summary for each group. Microsoft describes aggregate functions as calculating a single value from a set of values in its SQL Server aggregate function reference.
A window expression also calculates across related rows, but it returns a calculated value for each row in the window instead of collapsing those rows into one result. Microsoft’s SQL Server documentation for the OVER clause puts it this way: “A window function then computes a value for each row in the window.”
| Dimension | Aggregate with GROUP BY |
Window calculation with OVER |
|---|---|---|
| Result rows | Typically one result row per group | Retains the qualifying detail rows |
| Useful question | What is the total, average, or count for each group? | What is this row’s value relative to its group or ordered neighbors? |
| Typical examples | Payroll by department; sales by month; count by status | Department total beside each employee; running total; moving average |
| Main SQL construct | GROUP BY, sometimes with HAVING |
OVER, optionally with PARTITION BY, ORDER BY, and a frame |
When should you use GROUP BY?
Use GROUP BY when the report needs a summary rather than the individual records that produced it. For example, this query returns one row per department:
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minute#1 Best Overall
SELECT department_id,
SUM(salary) AS department_payroll,
AVG(salary) AS average_salary,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;The values in department_id define the groups; each aggregate calculates a summary within its department. Microsoft’s PostgreSQL-oriented training also covers aggregate functions with GROUP BY and HAVING in its aggregate functions and grouping module.
A grouped query generally cannot return an arbitrary employee-level column as if it were part of the summary. In a grouped result, select group columns and aggregate expressions, subject to the rules of the database engine and query.
When should you use a window function?
Use a window function when each detail row must remain visible but should also carry a calculation based on related rows. In SQL Server, the OVER clause defines the window for a function. PARTITION BY divides rows into independent calculation groups without grouping the query result; if omitted, the window can cover the whole result set.
Keep each employee and show the department total
SELECT employee_id,
department_id,
salary,
SUM(salary) OVER (PARTITION BY department_id) AS department_payroll
FROM employees;Each employee remains a separate result row, and the department payroll appears alongside that employee. Microsoft’s SQL Server examples use the same general approach to display order-level aggregates beside order-detail rows.
Calculate a running total
SELECT account_id,
transaction_date,
transaction_id,
amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY transaction_date, transaction_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_balance_change
FROM transactions;PARTITION BY account_id starts a separate calculation for each account. The ordering puts transactions in sequence; transaction_id breaks ties when dates repeat. The explicit ROWS frame includes every ordered row from the account’s first row through the current row. This calculates a cumulative change, not necessarily an account balance: an opening balance must be included, or the amounts must already represent the appropriate balance changes.
What do PARTITION BY, ORDER BY, and frames control?
PARTITION BY: Creates separate calculation windows, such as one window per department or account. It does not reduce the result to one row per partition.ORDER BYinsideOVER: Establishes logical order for calculations that depend on sequence, such as running totals. Include a tie-breaker if the ordering column can repeat and the intended row sequence matters.ROWSorRANGEframe: Limits which ordered rows participate in a calculation. Choose a frame that matches the intended calculation; do not assume defaults produce the desired cumulative or moving result.
In SQL Server, frame syntax requires ordering. Exact defaults, supported functions, frame behavior, and syntax vary across database engines and versions, so check the documentation for the engine you use. The examples here follow SQL Server’s documented OVER syntax; the concepts are useful more broadly, but the syntax and behavior should be verified before treating a query as portable.
How do NULL values affect aggregate counts?
SQL Server’s aggregate-function documentation says aggregates ignore NULL values, with COUNT(*) as the exception. Use COUNT(*) to count rows; use COUNT(column_name) to count only rows where that column is non-NULL. That difference matters when missing values are present.
Can you combine grouped aggregates and window functions?
Yes. They answer different questions and can be used in the same query when the desired result first needs a grouped summary and then a calculation across those summaries. Keep the intended grain clear: grouping changes which rows the query returns, while a window calculation operates across rows in its query result. Check your database’s rules for combining grouped expressions and window functions.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
