# SQL Aggregate Functions Cheat Sheet

A quick reference guide to using SQL aggregate functions.

This SQL Aggregate Functions Cheat Sheet is designed to be your companion whenever you’re using SQL for data analysis.

Aggregating data is essential for any meaningful data analysis. SQL provides a set of functions that allow you to include totals, averages, and counts in your reports and to extract the minimum and maximum value of any column of data.

In this Cheat Sheet, we’ll demonstrate the following SQL aggregate functions:

• `SUM()` – Returns the total of all values.
• `AVG()` – Returns the mean average of all values.
• `COUNT()` – Counts and returns the number of values.
• `MIN()` – Returns the smallest value.
• `MAX()` – Returns the largest value.

We’ll also show how to use aggregate functions:

• With `GROUP BY`.
• To order data.
• As a filtering condition.

If you’ve never used SQL, you might like to start by enrolling in our SQL Basics course. This course is suitable for absolute beginners; its 129 interactive exercises will quickly help you gain skills and confidence. You can learn from the comfort of your home – and you don’t need to install any software, as you’ll be using our online database through your browser.

## Common SQL Aggregate Functions

All examples in this cheat sheet use the following data held in a table named `orders`.

departmentcustomeritemqtytot_valuedate_paid
125Kettle1152024-01-15
118Toaster440
216Mug12182024-01-22
325Desk4120
216Dinner Plate12242024-01-22
318Office Chair2362024-01-17
120Lamp345
210Cutlery Set1302024-01-22

### SUM()

The `SUM()` function returns the total sum of a column of numbers.

Example:

```SELECT SUM(tot_value)
FROM orders;
```

Result:

SUM(tot_value)
328

For more information on how to use the `SUM()` function, see our cookbook How to Sum Values of a Column in SQL.

For further examples of the `SUM()` function, see our article SQL SUM() Function Explained with 5 Practical Examples.

### AVG()

The `AVG()` function returns the average value for a column of numbers.

Example:

```SELECT AVG(qty)
FROM orders;
```

Result:

AVG(qty)
4.875

For more information on how to use the `AVG()` function, see our cookbook How to Find the Average of a Numeric Column in SQL.

For further examples of using the `AVG()` function, see our article The SQL AVG() Function Explained with Examples

### COUNT()

The `COUNT()` function returns the number of rows (i.e. values) in a given column. There are three different ways of using this function.

#### COUNT(*)

This option gives you the total number of rows returned by your query.

Example:

```SELECT COUNT(*)
FROM orders;
```

Result:

COUNT(*)
8

#### COUNT(column_name)

This option gives you the number of rows in your query where the named column does not contain a `NULL` value.

Example:

The example shows the number of orders that have been paid for, i.e. orders where `date_paid` is not `NULL`.

```SELECT COUNT(date_paid)
FROM orders;
```

Result:

COUNT(date_paid)
5

#### COUNT(DISTINCT column_name)

This option gives you the number of unique values in the named column.

Example:

Let’s show the number of different customers who have placed orders.

```SELECT COUNT(DISTINCT customer)
FROM orders;
```

Result:

COUNT(DISTINCT customer)
5

For more information on the `COUNT()` function, see our cookbook  How to Count the Number of Rows in a Table in SQL.

For further examples of using the `COUNT()` function, see our article The SQL Count Function Explained With 7 Examples.

For more information on using `COUNT(DISTINCT)`, see How to Count Distinct Values in SQL

For more information on the difference between `COUNT(*)`, `COUNT(column)`, and `COUNT(DISTINCT)`, see What’s the Difference Between COUNT(*), COUNT(1), COUNT(column), and COUNT(DISTINCT)?

### MIN()

The `MIN()` function returns the smallest value in a column. This function works with most data types, including numbers, strings (the first in alphabetical order), and dates (i.e. the oldest date).

Example:

```SELECT MIN(tot_value)
FROM orders;
```

Result:

MIN(tot_value)
15.00

For more information on working with the `MIN()` function, see our cookbook How to Find the Minimum Value of a Column in SQL.

For further examples of the `MIN()` function, see our article SQL MIN and MAX Functions Explained in 6 Examples.

### MAX()

The `MAX()` function returns the largest value in a column. This function works with most data types, including numbers, strings (the last in alphabetical order), and dates (i.e. the most recent date).

Example:

```SELECT MAX(date_paid)
FROM orders;
```

Result:

MAX(date_paid)
2024-01-24

For more information on working with the `MAX()` function, see How to Find the Maximum Value of a Numeric Column in SQL.

For further examples, see SQL MIN and MAX Functions Explained in 6 Examples.

## Using SQL Aggregate Functions with GROUP BY

In the examples above, the aggregates were calculated from the entire table. However, you often need aggregates split into groups, e.g. to calculate orders’ total value by customer or the average quantity ordered by department.

In SQL, the `GROUP BY` clause creates the groups that we use for aggregation. This clause suppresses individual rows so that the report contains one row per defined group. If you group by customer, you will see one row for each customer. This row contains the summarized information that you’ve requested. You can group by more than one criterion; for example, you could group customers with their geographic area to get customers within each area.

For more information on grouping in SQL, see our article GROUP BY in SQL Explained.

Example:

```SELECT customer, SUM(tot_value)
FROM orders
GROUP BY customer;
```

Result:

customerSUM(tot_value)
1030.00
1642.00
1876.00
2045.00
25135.00

The column you’re grouping by is usually included in your `SELECT` list, otherwise the output is not meaningful. The `SELECT` list can only include the column you’re grouping by, your aggregate functions, and literal values. If a column is listed that is not used in an aggregate and not included in the `GROUP BY` clause, you’ll get an error message.

You can include more than one aggregate function in your query and you can group by more than one column. List them from major to minor groups, separated by commas.

If you need practice using aggregates with groups, see SQL Practice: 10 GROUP BY Practice Exercises with Detailed Solutions.

## Using SQL Aggregate Functions to Order Your Data

You may want to order your results based on an aggregate column. Because `ORDER BY` is computed after `GROUP BY`, you can use aggregate functions directly in the `ORDER BY` clause.

Example:

You may want to group the orders by customer and then show the customers ordered by the total value of their orders; this way, the customer with the highest value of orders appears at the top of the list.

```SELECT customer, sum(tot_value)
FROM orders
GROUP BY customer
ORDER BY sum(tot_value) DESC;
```

Result:

customerSUM(tot_value)
25135.00
1876.00
2045.00
1642.00
1030.00

You can specify an aggregate in the `ORDER BY` clause using exactly the same format you used when you specified it in your list of columns to be selected. The `DESC` clause orders the rows from highest to lowest.

For more information on sorting by aggregate functions, see these SQL cookbooks:

## Using SQL Aggregate Functions to Filter Your Data

Sometimes you’ll want to filter by an aggregate column. To filter by an aggregate, use the `HAVING` clause. Optionally, you can use this in conjunction with the `WHERE` clause to add further filters on non-aggregate columns.

Example:

You may want a list of customers who have paid for more than a given value of goods to include them in special offers.

```SELECT customer, SUM(tot_value)
FROM orders
WHERE date_paid IS NOT NULL
GROUP BY customer
HAVING sum(tot_value) > 30
ORDER BY customer;
```

Result:

customerSUM(tot_value)
1642.00
1836.00

To filter the rows by an aggregate function, you must use the `HAVING` clause. The `HAVING` clause immediately follows the `GROUP BY` clause. Optionally, you can also include a `WHERE` clause and an `ORDER BY` clause.