 # 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. Figure 1: Error message when not including all columns in an aggregate function or GROUP BY.

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!