How to Calculate the Average in SQL

If you need to calculate the average in SQL of a particular column’s values, built-in aggregate functions are the solution. Fortunately, all of the major database platforms will provide you with built-in functions to perform these calculations. However, there are considerations that you should be aware of when it comes to NULL values in your data. Let’s take a look at how to perform these calculations.

Getting a Basic Average in SQL

Before we get started, let’s run a couple of queries to make sure our sales_records table has the right values. These queries are based on the work we did in How to Multiply in SQL and How to Add in SQL. These queries will ensure we’re working with the same data in our sales_tax and transaction_amount fields. You can get the table structure and insert statements from the Dev Playbook Github Repository. (Check under the “Complete Guide to SQL Math Operators” section in the README.)

UPDATE sales_records SET
    sales_tax = unit_price * quantity * .075;

UPDATE sales_records SET
    transaction_amount = (unit_price * quantity) + sales_records.sales_tax + 5.99;

Note that in PostgreSQL, you will need to cast the 5.99 (our shipping and handling charge) as a money value: 5.99::money.

All of the major database platforms provide built-in functions for calculating and average. These functions are called “aggregate functions”. Let’s say that we want to determine the average unit price from our sales_records table. The simplest way to do this is using the AVG() function. The function is the same in Microsoft SQL Server, MySQL, and PostgreSQL.

SELECT avg(unit_price)
FROM sales_records;

You will see some differences across the three servers though. In MS SQL Server and PostgreSQL, we’re using the money data type and in MySQL we’re using a decimal data type. Because of this, we’re going to see slightly different results. MS SQL Server gives us a value of 27.5685 when we run this query. MySQL gives us a value of 27.56857143 – a much more precise number.

PostgreSQL will work slightly differently. Because the AVG() function in PgSQL does not accept a money data type, we will have to cast the field to a numeric field to get a result.

SELECT avg(unit_price::numeric)
FROM sales_records;

This will give us the most precise value of 27.5685714285714286.

The Average Function and Group By Clause

Any time you use an aggregate function in SQL, every field selected in the query must be included in either an aggregate function or a GROUP BY clause.

Our query above will give us the average unit price of all records in our table. However, more realistically, we might want to know what is the average transaction amount by customer. This can help us see who our big spenders are. So let’s write a query to see that.

SELECT avg(transaction_amount) AS average_transaction_amount,
       customer_name
FROM sales_records;

If we run this query in Microsoft SQL Server, we’ll get the following error message: “Column ‘sales_records.customer_name’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.” MySQL and PostgreSQL will give us similar error messages.

In order to fix this, we will add a GROUP BY clause and include the customer_name field in it. Once we run this query, we’ll get the results we want.

SELECT avg(transaction_amount) AS average_transaction_amount,
       customer_name
FROM sales_records
GROUP BY customer_name;

Note that you can also use ORDER BY with an aggregate function. If we want to see our biggest average spenders at the top, we can add ORDER BY avg(transaction_amount) DESC to our query and voila! There we have it.

SELECT avg(transaction_amount) AS average_transaction_amount,
       customer_name
FROM sales_records
GROUP BY customer_name
ORDER BY avg(transaction_amount) DESC;

Again, if you’re using PostgreSQL, don’t forget to cast the value as numeric within the AVG() function in both the SELECT and the ORDER BY clauses.

Average in SQL with NULL Values

As with any sort of mathematical operation or aggregate function, NULL values are treated differently from numeric values. When calculating the average in SQL, NULL values will be excluded from the calculation.

Let’s say that we do a basic calculation on our average transaction amount in our table as it is. We know that we have seven records in the table now. As of right now, all of those records have a value in the transaction_amount field.

SELECT avg(transaction_amount) AS average_transaction_amount
FROM sales_records;

The value returned is the average of the seven records: 66.0425. But what if one of those records had a NULL transaction amount? Let’s run an update and then we will run some test queries to see what results we get.

UPDATE sales_records SET
    transaction_amount = NULL
WHERE id = 2;

SELECT COUNT(*), SUM(transaction_amount), AVG(transaction_amount)
FROM sales_records;

In our second query, we’re checking the count of total records, the sum of the transaction amounts, and the average of the transaction amounts. Note that even though we have 7 records in our table, the SUM(transaction_amount) will include only non-NULL values and the AVG(transaction_amount) will be the sum divided by the count of non-null values, in this case six. So the average is actually: 386.1643 / 6 = 64.3607.

If you want to assume that a NULL value is equal to zero, you can use the COALESCE() function to substitute zero for NULL values. Be very careful that this is your intent, though, because it can alter your results. This also works in all three databases – MS SQL Server, MySQL, and PostgreSQL.

SELECT COUNT(*), SUM(transaction_amount), AVG(transaction_amount), AVG(COALESCE(transaction_amount, 0))
FROM sales_records;

Calculate a Weighted Average in SQL

Sometimes we want to be able to calculate a weighted average in SQL. This can be helpful when we’re comparing different quantities of items at different values. In our example table, we might want to calculate the average per-unit price across all orders. In this case, we have different quantities for each order and each unit has its own price, or value. The equation we will use is:

weight_average = (total quantity * per unit cost) / total quantity

To do this, we will actually use the SUM() function around a multiplication operation, paired with a division operation. (See detailed articles for these in our Complete Guide to SQL Math Operators.)

SELECT SUM(quantity * unit_price) / SUM(quantity) AS weighted_avg_unit_price,
       SUM(quantity) AS total_quantity,
       SUM(quantity * unit_price) AS total_unit_price,
       AVG(unit_price) AS average_unit_price
FROM sales_records;

In the first calculation in our SELECT statement, we get the SUM of the quantity * unit_price. This represents the total amount paid for all units and excludes our sales tax and $5.99 shipping and handling fees. We then divide this amount by the SUM of the quantity of units sold. This tells us the weighted average of our per unit price. We get a weighted average of 12.6141 in this case.

We’ve included here the total quantity, total unit price, and average unit price so that you can see the values that go into the calculation and compare the weighted average unit price to the straight average unit price.

That pretty much wraps up calculating an average in SQL. It is generally very straightforward using the AVG() function and fortunately, it works very consistently across the major database platforms. Happy coding!