# 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:

```sql
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

```sql
SELECT SUM(UnitsInStock) AS UnitsInStock
FROM Products;
```

| UnitsInStock |
|---|
| 3119 |

```sql
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.

```sql
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:

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

| AverageQuantity |
|---|
| 23.812993 |

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

## Aggregates with WHERE

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

```sql
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 `NULL`s and the whole-number averages, and the answers are right.

## Key terms

- [COUNT](https://www.ask-codey.com/glossary/count)
- [SUM](https://www.ask-codey.com/glossary/sum-database)
- [AVG](https://www.ask-codey.com/glossary/avg)
- [MIN](https://www.ask-codey.com/glossary/min)
- [MAX](https://www.ask-codey.com/glossary/max)

## Cheat sheets

- [Common Aggregate Functions](https://www.ask-codey.com/cheat-sheets/aggregate-functions-in-t-sql-common-aggregate-functions-cheat-sheet)

Source: https://www.ask-codey.com/lessons/aggregate-functions-in-t-sql-common-aggregate-functions