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.

SUM()

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

Example:

SELECT SUM(tot_value) 
FROM orders;

Result:

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:

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

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:

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:

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:

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.

For more information, see these articles:

• How to Use SUM() with GROUP BY: A Detailed Guide with 8 Examples
• How to Use COUNT() with GROUP BY: 5 Practical Examples

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:

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:

• How to Order Rows by Group Sum in SQL
• How to Order by COUNT() in SQL

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:

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.

For more information, see these SQL cookbooks:

• How to Filter Records with the Aggregate Function COUNT()
• How to Filter Records with the Aggregate Function SUM()
• How to Filter Records with the Aggregate Function AVG()
• How to Find Rows with the Minimum Value
• How to Find Rows with Maximum Value

Beyond the SQL Aggregate Functions Cheat Sheet

If you’d like to boost your SQL skills, check out our offer of interactive SQL courses. We have a wide range of courses at different levels of SQL proficiency. We also have specialized courses covering the use of SQL in data analysis.

I particularly recommend our SQL Practice learning path. It contains 9 courses focused on comprehensive SQL practice, including SQL JOINs and aggregate functions. Exercises are done on realistic datasets, such as a database for a university and a store. We also publish a new SQL practice course in our Monthly SQL Practice track.

You can get all the courses above and more with our All Forever SQL Plan.  It gives you lifetime access to all current and future courses on our platform. Happy learning!