Aggregate functions collapse many rows into one value: a count, a total, an average, a minimum or a maximum. Combined with GROUP BY they return one row per group:
SELECT Region,
COUNT(*) AS Sales,
COUNT(Amount) AS Invoiced,
SUM(Amount) AS Total,
AVG(Amount) AS Average,
MIN(Amount) AS Smallest,
MAX(Amount) AS Largest
FROM dbo.Sales
GROUPBY Region
ORDERBY Region;
Region Sales Invoiced Total Average Smallest Largest
East 3 3 1000.00 333.333333 200.00 500.00
North 1 1 400.00 400.000000 400.00 400.00
West 3 2 1000.00 500.000000 100.00 900.00
The examples are written for SQL Server and use this table. The core syntax (COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, OVER) is standard SQL and works the same way in other databases.
Error 8152 never said which column. Message 2628 on SQL Server 2019+ (or trace flag 460 on 2016/2017) names it. Then fix the data or the column, and watch for silent truncation.
Look at the West row above: three sales, but COUNT(Amount) is 2 and the average is 500, not 333. Every aggregate except COUNT(*) ignores NULL values.
Expression
Result on dbo.Sales
What it counts
COUNT(*)
7
Rows
COUNT(Amount)
6
Rows where Amount is not NULL
COUNT(DISTINCT Region)
3
Different non-NULL values
SUM(Amount)
2400.00
AVG(Amount)
400.000000
2400 / 6, not 2400 / 7
If a missing amount should count as zero, say so: AVG(ISNULL(Amount, 0)) divides by 7. When an aggregate skips a NULL, SQL Server also returns the message Warning: Null value is eliminated by an aggregate or other SET operation. It is informational, not an error.
With no rows at all, the behaviour differs by function. COUNT returns 0; SUM, AVG, MIN and MAX return NULL:
SELECTCOUNT(*) AS Cnt, SUM(Amount) AS Total
FROM dbo.Sales
WHERE Region ='South'; -- 0, NULL
Wrap the total in ISNULL(SUM(Amount), 0) if the caller expects a number. Add GROUP BY Region to that query and it returns no rows at all, because there is no group to report.
Result types and overflow
The return type follows the input type, which produces two classic surprises.
AVG of an integer column is an integer:
SELECTAVG(v) AS IntAvg, AVG(v *1.0) AS DecimalAvg
FROM (VALUES (1), (2)) AS t(v); -- 1 and 1.500000
SUM of an int column returns int, and fails with error 8115 (Arithmetic overflow error converting expression to data type int) once the total passes 2,147,483,647. Cast inside the aggregate: SUM(CAST(Quantity AS bigint)). COUNT also returns int; for tables that may exceed two billion rows use COUNT_BIG.
GROUP BY
Every column in the SELECT list must either appear in GROUP BY or sit inside an aggregate. Otherwise SQL Server cannot know which row's value to show:
SELECT Region, Rep, SUM(Amount)
FROM dbo.Sales
GROUPBY Region;
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.Sales.Rep' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Add Rep to the GROUP BY, or aggregate it (MIN(Rep), COUNT(DISTINCT Rep)).
You can group by an expression, but you must repeat the expression; a column alias from the SELECT list is not visible in GROUP BY:
SELECT DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1) AS SaleMonth,
SUM(Amount) AS Total
FROM dbo.Sales
GROUPBY DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1)
ORDERBY SaleMonth;
SaleMonth Total
2026-01-01 1700.00
2026-02-01 300.00
2026-03-01 400.00
On SQL Server 2022 and later, DATETRUNC(month, SaleDate) does the same job more readably. If the grouping column contains NULLs, they all fall into a single group.
Subtotals with ROLLUP
ROLLUP adds a grand-total row without a second query:
SELECT Region, SUM(Amount) AS Total
FROM dbo.Sales
GROUPBYROLLUP (Region);
Region Total
East 1000.00
North 400.00
West 1000.00
NULL 2400.00
The NULL in the last row marks the total. GROUPING(Region) returns 1 on that row, which lets you tell it apart from a real NULL region. GROUPING SETS and CUBE cover more complex combinations.
WHERE filters rows, HAVING filters groups
SQL Server evaluates the clauses in this order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. WHERE runs before grouping and cannot see aggregates. HAVING runs after grouping and can:
SELECT Rep, SUM(Amount) AS Total
FROM dbo.Sales
WHERE SaleDate >='2026-01-01'AND SaleDate <'2026-03-01'-- rowsGROUPBY Rep
HAVINGSUM(Amount) >=300-- groupsORDERBY Total DESC;
Rep Total
Carla 900.00
Asha 800.00
Putting an aggregate in WHERE raises error 147. Putting a plain row filter in HAVING works but is wasteful: the rows are grouped first and thrown away afterwards. Filter rows in WHERE so indexes can be used.
To count only some of the rows in each group, put the condition inside the aggregate. COUNT ignores the NULL that CASE returns when nothing matches:
SELECT Region,
COUNT(*) AS AllSales,
COUNT(CASEWHEN Amount >=400THEN1END) AS LargeSales
FROM dbo.Sales
GROUPBY Region; -- East 3/1, North 1/1, West 3/1
Window aggregates: totals without collapsing rows
GROUP BY returns one row per group. Add an OVER clause instead and the same aggregate is computed across a set of related rows while every detail row stays in the result:
SELECT SaleID, Region, Amount,
SUM(Amount) OVER (PARTITIONBY Region) AS RegionTotal,
SUM(Amount) OVER (PARTITIONBY Region
ORDERBY SaleDate, SaleID
ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM dbo.Sales
ORDERBY Region, SaleDate, SaleID;
SaleID Region Amount RegionTotal RunningTotal
1 East 500.00 1000.00 500.00
2 East 300.00 1000.00 800.00
3 East 200.00 1000.00 1000.00
7 North 400.00 400.00 400.00
4 West 900.00 1000.00 900.00
5 West NULL 1000.00 900.00
6 West 100.00 1000.00 1000.00
PARTITION BY plays the role of GROUP BY: the aggregate restarts for each region.
ORDER BY inside OVER turns the total into a running total.
An empty OVER () treats the whole result as one partition, which gives a percentage of total in one pass: Amount * 100.0 / SUM(Amount) OVER ().
Two things to watch:
Write the frame explicitly. With ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with equal sort values as one unit, so tied rows all show the same running total. ROWS UNBOUNDED PRECEDING advances one row at a time and is usually faster as well. Ordering by a unique combination (SaleDate, SaleID) keeps the result deterministic.
DISTINCT is not allowed with OVER.COUNT(DISTINCT Rep) OVER (...) is rejected; compute distinct counts in a grouped subquery and join back.
Window functions are evaluated after WHERE, GROUP BY and HAVING, so you cannot filter on one directly. Wrap the query in a CTE or derived table and filter outside it.
Other aggregates worth knowing
STRING_AGG(expr, separator) concatenates values in a group.
STDEV, STDEVP, VAR, VARP return standard deviation and variance.
APPROX_COUNT_DISTINCT(expr) (SQL Server 2019 and later) returns an approximate distinct count using far less memory than COUNT(DISTINCT ...) on very large tables.
Common mistakes
Expecting AVG or COUNT(column) to include NULL rows.
Using COUNT(*) > 0 to test whether rows exist. EXISTS (SELECT 1 ...) can stop at the first match.
Filtering rows in HAVING instead of WHERE.
Adding columns to GROUP BY just to silence error 8120. That changes the grouping and usually multiplies the rows.
Joining to a child table before aggregating, which duplicates parent rows and inflates SUM. Aggregate the child table in a subquery first, then join.
A query-first method: find the expensive queries, read the plan for signs the missing index DMVs never report, build the index online safely, then verify and roll back.
