# Common Aggregate Functions

What COUNT, SUM, AVG, MIN and MAX return, and how each one treats NULL values.

Apprentice · SQL · MySQL · Updated November 16, 2026

By Gregor Dzierzon

SQL has five aggregate functions you will use all the time. Each one turns a column of many rows into a single value. These examples use the world database.

## COUNT(\*)

Returns a total count of all rows.

The following query returns a count of all countries in the world database.

```sql
SELECT COUNT(*)
FROM country;
```

## COUNT(&lt;column>)

Returns a count of all values in a column. NULL values are NOT counted.

The following query returns a count of all independent countries.

```sql
SELECT COUNT(IndepYear)
FROM country;
```

## COUNT(DISTINCT &lt;column>)

Returns how many different values a column holds. NULL values are not counted, and a value that repeats is counted only once.

The following query returns a DISTINCT count of all regions.

```sql
SELECT COUNT(DISTINCT Region)
FROM country;
```

## SUM(&lt;column>)

Returns the sum of all values in the specified column.

The following query returns the sum of the population in all countries.

```sql
SELECT SUM(Population) AS total_population
FROM country;
```

## AVG(&lt;column>)

Returns the average of all values in the specified column. NULL values are NOT included in the calculation.

The following query returns the average life expectancy of ALL countries.

```sql
SELECT AVG(LifeExpectancy) AS average_life_expectancy
FROM country;
```

## MIN(&lt;column>)

Returns the smallest value of the specified column in all rows. NULL values are NOT considered.

The following query returns the lowest GNP of ALL countries.

```sql
SELECT MIN(GNP) AS min_gnp
FROM country;
```

## MAX(&lt;column>)

Returns the largest value of the specified column in all rows, NULL values are NOT considered.

The following query returns the highest GNP of ALL countries.

```sql
SELECT MAX(GNP) AS max_gnp
FROM country;
```

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

| product_name | category_id | unit_price |
|---|---|---|
| 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:

| beverages | cheapest | priciest |
|---|---|---|
| 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: `beverages` (how many beverages), `cheapest` (the lowest `unit_price`) and `priciest` (the highest `unit_price`).
- Look only at the rows whose `category_id` is `1`.
- The answer is one row.

Practiced live on the lesson page: write the SQL right there and check it.

## Cheat sheets

- [Calculations vs Aggregations, and the Common Aggregate Functions](https://www.ask-codey.com/cheat-sheets/aggregate-functions-calculations-vs-aggregations-cheat-sheet)

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