# 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 15, 2026

By Gregor Dzierzon

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

## 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 lowest `UnitPrice`) and `Dearest` (the highest `UnitPrice`).
- Look only at the rows whose `CategoryId` is `1`.
- 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 `NULL`s and the whole-number averages, and the answers are right.

## Key terms

- [AVG](https://www.ask-codey.com/glossary/avg)

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