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
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'tNULL, andCOUNT(DISTINCT column)the different values.SUMadds the values up,AVGaverages them, andMINandMAXfind the smallest and largest, in text and dates too.- Every aggregate except
COUNT(*)skipsNULL: the average of three prices and oneNULLis the average of the three. - The average of whole numbers drops the decimals. Write
AVG(Quantity * 1.0)to keep them. WHEREfilters 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 |
Do it: size up the Beverages shelf
The buyer is reviewing the Beverages shelf (category 1). She wants three numbers in one row: how many beverages there are, the lowest price, and the highest price.
The Products table (the Tables tab shows the SQL that makes it) has these rows:
| ProductName | CategoryId | UnitPrice |
|---|---|---|
| Chai | 1 | 18.00 |
| Chang | 1 | 19.00 |
| Aniseed Syrup | 2 | 10.00 |
| Chef Anton's Cajun Seasoning | 2 | 22.00 |
| Grandma's Boysenberry Spread | 2 | 25.00 |
| Guaraná Fantástica | 1 | 4.50 |
| Côte de Blaye | 1 | 263.50 |
| Ipoh Coffee | 1 | 46.00 |
Five of them are in category 1: Chai, Chang, Guaraná Fantástica, Côte de Blaye and Ipoh Coffee. The lowest price is $4.50 and the highest is $263.50. So your query should give exactly this one row:
| Products | Cheapest | Dearest |
|---|---|---|
| 5 | 4.5 | 263.5 |
Your job: write one SELECT query that gives these three numbers for the Beverages.
- Show three columns, in this order:
Products(how many products),Cheapest(the lowestUnitPrice) andDearest(the highestUnitPrice). - Look only at the rows whose
CategoryIdis1. - The answer is one row.
Practiced live on the lesson page: write the SQL right there and check it.
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.