Common Aggregate Functions

COUNT counts rows or values, SUM adds them, AVG averages them, and MIN and MAX find the smallest and largest. All but COUNT(*) skip NULL.

Apprentice · T-SQL · SQL · Updated November 3, 2026

After this lesson you can

Count rows and values with COUNT(*), COUNT(column) and COUNT(DISTINCT column)
Total, average and find the smallest and largest values with SUM, AVG, MIN and MAX

Key takeaways

COUNT(*) counts every row, COUNT(column) the rows where that column isn't NULL, and COUNT(DISTINCT column) the different values.
SUM adds the values up, AVG averages them, and MIN and MAX find the smallest and largest, in text and dates too.
Every aggregate except COUNT(*) skips NULL: the average of three prices and one NULL is the average of the three.
The average of whole numbers drops the decimals. Write AVG(Quantity * 1.0) to keep them.
WHERE filters the rows before they're aggregated.

Five aggregate functions answer most questions about a table as a whole.

COUNT

COUNT comes in three forms, and they count different things:

SELECT COUNT(*) AS Customers
    , COUNT(Region) AS WithRegion
    , COUNT(DISTINCT Country) AS Countries
FROM Customers;

Customers	WithRegion	Countries
91	31	21

Form	Counts
`COUNT(*)`	Every row: 91 customers
`COUNT(column)`	The rows where that column isn't `NULL`: 31 customers have a region
`COUNT(DISTINCT column)`	The different values in the column, without `NULL`: 21 countries

So COUNT(*) and COUNT(column) can disagree, and the difference is how many rows have NULL there. Of 830 orders, 809 have a ShippedDate: the other 21 haven't shipped yet.

SUM, AVG, MIN, MAX

SELECT SUM(UnitsInStock) AS UnitsInStock
FROM Products;

UnitsInStock
3119

SELECT AVG(UnitPrice) AS AveragePrice
    , MIN(UnitPrice) AS Cheapest
    , MAX(UnitPrice) AS Dearest
FROM Products;

AveragePrice	Cheapest	Dearest
28.8663	2.50	263.50

Function	Returns
`SUM(column)`	The values added up
`AVG(column)`	The average of the values
`MIN(column)`	The smallest value
`MAX(column)`	The largest value

They all skip NULL. An average of three prices and one NULL is the average of the three. MIN and MAX work on text and dates too: MIN(OrderDate) is the first order's date, MAX(OrderDate) the latest.

Watch out: the average of whole numbers

Quantity is a whole-number column (a smallint), and the average of whole numbers is an int: the decimal part is dropped.

SELECT AVG(Quantity) AS AverageQuantity
FROM OrderDetails;

AverageQuantity
23

The true average is about 23.8. Multiply by 1.0 first, to make the values decimals:

SELECT AVG(Quantity * 1.0) AS AverageQuantity
FROM OrderDetails;

AverageQuantity
23.812993

It's the same rule as dividing ints in C#.

Aggregates with WHERE

WHERE filters the rows before they're aggregated. How many products are in category 1?

SELECT COUNT(*) AS Products
FROM Products
WHERE CategoryId = 1;

Products
12

Check yourself

2 questions to try on the lesson page.

So what

Five functions, and a table of thousands of rows answers in one line: how many, how much in all, on average, at the least, at the most. Mind the NULLs and the whole-number averages, and the answers are right.

Key terms

Cheat sheets