How to Use the SQL AVG Function in Your SQL Queries

Today, businesses thrive on data. They use data collected from sales, website visitors, and more to generate useful information that they can use to make decisions about their business. Forecasting trends, generating dashboards, and data analysis are important parts of keeping ahead of the competition. Other SQL users simply want more functionality than the basic SQL queries bring them.

Tables are where enterprises access most of the data stored in relational databases. Many people don’t know that you can run data analysis type functions in SQL itself. SQL analytical functions allow users to run calculations. SQL analytical functions include SUM for summing values, COUNT for counting values, and AVG for finding the average of values. In this article, we will look at the AVG function and all the things you can use it for in your SQL queries.

What does SQL AVG do?

AVG is one of the SQL functions known as an aggregate function. Many of the queries that we write in SQL only operate on a single row. Aggregate functions, which are also called group functions, operate on a group of rows and generate a calculated value based on the values in those rows. But the results of aggregate functions like SUM, COUNT, and AVG are not actually stored in the database.

This is an important fact because the results of these functions are not just data that we stored and simply retrieved when we need it. SQL can actually do more than store data for us. We can use these analytical functions to apply mathematical operations on a set of rows or groups of values returned by another SQL expression. This means that you can generate information that you didn’t have before from data you have stored in your database tables. You can do this using SQL’s built-in functions instead of resorting to writing your calculations in another programming language that parses the SQL results.

Aggregate functions like AVG can calculate values across every row in a table on data that you group with the GROUP BY clause or because of an SQL expression. Multi-group functions like AVG can generate the average of the entire dataset or create averages for individual group totals. This may not seem like much, but with functions like AVG, you can do business analytics with pure SQL. When we get to the examples, we will look at how we can use them on product data.

The AVG function is just the beginning of the analytical functions you can add to your SQL for more advanced queries. It is a multi-group function like SUM and COUNT that can work across a range of records. If you combine these multi-group functions with some of SQL’s single group functions, like MIN, MAX, and VAR, then you can create more complicated calculations like standard deviation, which means you can do real business analytics with pure SQL.

Retrieving your business analytics data directly from your database tables is an efficient way to get the data you need. Still, it can be impossible to do with standard SQL, which will only give you the information you actually stored. Without the AVG function, you would have to query the database in a programming language and loop the results to generate an average of a column. But fortunately, you don’t. Let’s get more familiar with the SQL AVG function by looking at its syntax.

SQL AVG syntax

SQL has a standard syntax that is defined by the American National Standards Institute, also known as ANSI. But a standard is just a standard, and each relational database engine that uses SQL may or may not stick to those standards depending on the keyword you’re discussing and the engine you’re using. The AVG function syntax varies slightly depending on your preferred relational database.

The syntax for the SQL AVG function for MySQL, PostgreSQL, and SQL Server is:

AVG([ALL|DISTINCT]expression1, expression2, expression3 ….)

The syntax for DB2 & Oracle is:

AVG([ALL|DISTINCT] expression1, expression2, expression3..) OVER (windows_clause)

Parameters

ALL: Applies to all values

DISTINCT: Applies to unique values only

Expression: Argument made up of a single variable, constant, scalar function, or column name

Typical usage:

SELECT AVG (Column/Field) AS (New Field) FROM (Table)

In this query, we used the AS keyword to give a name to the average that it identified.

The next section will run through some examples of SQL AVG in real-world scenarios with suggested code as examples. All the examples will use the table we will get to below for reference and values.

SQL AVG example queries

For the example queries in this article, we will use a database table that looks like the table below. The table contains the products a grocery store has for sale, with the product name, price, cost, and a couple of IDs.

ProductID	ProductName	VendorID	Price	Cost
1	Frozen Strawberry	101	18	6
2	Blueberry Juice	102	19	6
3	Maple Syrup	101	10	4
4	Plain Yogurt	107	21.35	7
5	Sugar	102	25	8
6	Steak	107	18	7

Calculating a simple average of a column with AVG

In this query, we use the SELECT AVG function to calculate the average in the column Price. Since there is no WHERE clause, it calculates the average across every record in the table.

SELECT AVG(Price) AS PriceAverage FROM Products;

Here is the result of that query. The average price for a product that the company sells is $18.56​.

PriceAverage

18.56

Explanation: In this example, the AVG function calculates the average of the Price column in the table, Products, using the alias PriceAverage, which we assign with the AS keyword.

Using AVG as a condition in a WHERE clause

Let’s say we want to find all the products that are above our average price. Here we would have to find the average price with one query and use that as a sub-query of a query that compares its result against the Price column. Here is a query that will do that for us:

SELECT ProductName, Price FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

Here are results from that query:

ProductName	Price
Blueberry Juice	19
Plain Yogurt	21.35
Sugar	25

Explanation: In this example, the AVG function first calculates the average price for the column Price in the table Products. It selects those values that are greater than the average value and lists them under ProductName and Price.

Using SQL AVG with a WHERE clause

Now we just found out that most people buy products from the store that are between 10 and 25 dollars. We want to find the average of those products. Here is the query to find that value.

SELECT AVG(Price) AS AvgPrice FROM Products WHERE Price > 10 AND Price <= 25;

Here is the result of that query. The average cost for products in this range is $19.45.

AvgPrice
19.45

Explanation: The WHERE clause selects the prices that are greater than 10 and less than or equal to 25. AVG then finds the average of the prices in that window.

Using SQL AVG with GROUP BY

The last two queries used the WHERE clause to filter the rows we are plan to use the AVG function on. In one query, we filtered the results by a result from the AVG function. In the second, we used WHERE to filter the results that it will use to generate the average. Now we are going to use the GROUP BY clause to do something similar. We are going to filter a subset of the table to use in our average. Only this time, instead of returning one record, we will return multiple.

Let’s say you wanted the average price of a product by the VendorID. To get the average price of each, you could run three queries.

SELECT AVG(Price) AS PriceAverage FROM Products WHERE VendorID = 101;
SELECT AVG(Price) AS PriceAverage FROM Products WHERE VendorID = 102;
SELECT AVG(Price) AS PriceAverage FROM Products WHERE VendorID = 107;

These queries will give you the results you need, but why run three of them when you can run one. GROUP BY will allow you to get the same results with one query. Here is that query:

SELECT VendorID, COUNT(*), AVG(Price) AS AvgPrice
FROM Products GROUP BY VendorID;

Here are the results in one record set:

VendorID	Count	AvgPrice
101	2	14
102	2	22
107	2	19.675

Explanation: In this example, the GROUP BY clause groups the records by the VendorID, returning only one row per each VendorID. Because of that, we can only use aggregate functions as our other results, like COUNT and AVG. If we tried to add another column like the Price column by itself, the query would throw an error.

Using both SQL AVG and SQL SUM

We can also use the AVG function along with other SQL analytical functions. This example uses the SUM function that adds all the values in the column Price and then uses the AVG function to calculate the average.

SELECT SUM(Price) AS PriceSum, AVG(Price) AS PriceAverage FROM Products;

Here is the result of that query:

PriceSum	PriceAverage
111.35	18.56

Explanation: In this example, we select the sum of the prices using the PriceSum label and the average of all the prices using the PriceAverage label from the Prices table. The sum of all the prices is $111.35, and the average is the same value we got before.

Using SQL AVG with CAST

The AVG function returns the average of a set of expressions with default decimal places. However, you can use the CAST function in conjunction with AVG to increase or decrease the number of decimal places. When converting decimal and numeric data types, CAST is better at preserving the decimal places. The AS-DECIMAL is used along with the specified type when using the CAST to stipulate the decimal places.

Syntax

CAST (expression) AS [Data Type] [specified format]

Parameters

Expression: Expression made up of a single constant, variable, scalar function, or column name

Data Type: CHAR(), VARCHAR, DECIMAL, FLOAT

Specified Format: The required specified format

Using the Cast function with AVG

SQL AVG can use CAST both inside and outside of the AVG function. Here is an example of each.

SELECT CAST(AVG(Price) AS DECIMAL(10,2)) FROM Products

Here is the result of that query:

Price

18.56

Explanation: This example changed nothing about our result, but it guaranteed that we got an amount back that doesn’t have fractional cents in it. By casting the average to a DECIMAL(10,2) type, where 10 is the precision for the decimal or the maximum number of digits we want and 2 is the scale or the number of digits we want behind the decimal point.

To demonstrate how to use CAST inside AVG, let’s use our query where we calculated the average price for each product by the VendorID, but let’s cast each average to a decimal this time to make sure that we get the prices with no fractional cents.

SELECT VendorID, COUNT(*), AVG(CAST(Price AS DECIMAL(10,2))) AS AvgPrice
FROM Products GROUP BY VendorID;

Here is the result of that query:

VendorID	Count	AvgPrice
101	2	14
102	2	22
107	2	19.68

Explanation: First, you need to retrieve and count the number of VendorIDs. Then, you average the corresponding prices grouped by VendorID. This will return an average price to two decimal places for each VendorID. Notice that the average for VendorID 107 now has an average of $19.68 instead of ​$19.675.

Using DISTINCT with SQL AVG

Another thing you can do when you are trying out SQL queries that use AVG is use DISTINCT with the column you are averaging. This will remove all the duplicate values from the column and calculate the average of the remaining values. Here we add DISTINCT to the first basic query we created.

SELECT AVG(CAST(DISTINCT Price AS DECIMAL(10,2))) AS PriceAverage FROM Products;

The result of this query will be this:

PriceAverage

18.67

Explanation: You should notice that this average is slightly lower than the one we got from our original query. This is because there is a duplicate value of 20 in the price column that the DISTINCT clause used in the query removed.

Using AVG on multiple columns

So far, we have been calculating our averages based on the Price column. There is also another numeric column that we can use to calculate an average, Cost. In the following query, we calculate the average of both the Price column and the Cost column. We are also using CAST to retrieve numeric values with only two digits past the decimal point.

SELECT
	AVG(CAST(Price AS DECIMAL(10,2))) AS PriceAverage
	AVG(CAST(Cost AS DECIMAL(10,2))) AS CostAverage
FROM Products;

Here are the results of this query:

PriceAverage	CostAverage
18.56	6.33

Explanation: Here, we use CAST on both the Price and Cost columns, and then we take the average of the resulting decimal values.

Using a SQL expression in the AVG function

Our products table has both a Cost column and a Price column, so we could write a query like this to figure out each of the products’ gross profit.

SELECT *, (Price - Cost) AS Profit FROM Products;

For this query, we will get the following result set:

ProductID	ProductName	VendorID	Price	Cost	Profit
1	Frozen Strawberry	101	18	6	12
2	Blueberry Juice	102	19	6	13
3	Maple Syrup	101	10	4	6
4	Plain Yogurt	107	21.35	7	14.35
5	Sugar	102	25	8	17
6	Steak	107	18	7	11

Now we are returning calculations that we can actually use in business. Still, we can also use an SQL expression like the one we used in our last query inside the AVG function, and this will give us the average profit on our products. Here is that query:

SELECT AVG(Price - Cost) AS Profit FROM Products;

The result of that query will be:

Profit

12.23

Explanation: The SQL AVG can accept a SQL expression and average the return value of that expression across the range of selected records. By wrapping the value of Cost subtracted from Price, we can get the average profit across all the records.

Conclusion

You may be happy using standard SQL clauses like SELECT and WHERE — and they are powerful when you are trying to retrieve data from a database — but you should give SQL’s analytical functions a try. AVG, along with COUNT and SUM, will add more power to your queries. Using them, you can return calculated data directly from a database query instead of relying on external programming languages and get the answers you need quicker and more efficiently. The SQL AVG will return the average of a column, a range of rows in a column, or the result of another SQL expression. Combining AVG with other SQL commands and clauses will give those queries even more power and flexibility.