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
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:
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
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!
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?