The SQL AVG() Function Explained With Examples

We explain the SQL AVG() function with practical examples, covering how and where you can and cannot use it.

The average is probably one of the most widely used metrics to describe some characteristics of a group. It is so versatile and useful that it can describe something about almost anything.

If you like sports, you see things like average runs per game in baseball, average assists per game or per season in basketball, and so on. Every day, you hear statements like “on average, it rains 10 days a month in this city” or “the temperature remains above 70 degrees on average during summer.”

In this article, I will cover the usage of the SQL AVG() function with some real-life examples to help you make good use of it in practical situations. Read through!

The best way to practice SQL, including the AVG() function, is our interactive SQL Practice Set course. It contains 88 hands-on exercises to help you practice SQL on real-world problems.

What Does the SQL AVG() Function Do?

The SQL AVG() function is used to find the average of values over records from a table.

To understand this with an example, consider the following table that contains employee records.

employees

Example 1

Let’s say you want to find out the average skill level of the employees. You can use the SQL AVG() function. Here’s the query:

SELECT AVG(skill_level)
FROM employees;

This is the result:

The AVG() function takes a column name as its argument (also known as the operand) and then computes the average for all values in the column. So, in this case, our query returns the average of all values in the skill_level column.

You may notice the result is displayed with many decimal places. Since you rarely need it that precise, you might want to round this number to the nearest integer. You can use AVG() nested in other SQL functions like ROUND(), like this:

SELECT ROUND(AVG(skill_level))
FROM employees;

The result now looks like this:

The function inside the parentheses is evaluated first. So, the query first computes the average then rounds the result to give you the value 19.

Example 2

In the example above, we used AVG() in the SELECT part of the query. Let’s go through some more SQL AVG() examples.

Say you want to get a list of the employees whose salaries are above the company average. If you are new to SQL, your first instinct may be to write something like this:

SELECT lastname
FROM   employees
WHERE  annual_salary > AVG(annual_salary);

When you run it, it outputs something like this:

ERROR 1111 (HY000): Invalid use of group function

This query throws an error, because you cannot use AVG() in a WHERE condition. Since AVG() is a group function, you can only use it in a SELECT statement or in a HAVING clause.

Instead, run the following query:

SELECT   lastname, annual_salary
FROM     employees
WHERE    annual_salary > (SELECT AVG(annual_salary)
		 		    FROM employees);

Here’s the result:

The query inside a main query is called a subquery. Here, we first use a subquery (highlighted below) to get the average value of annual_salary from the employees table. We then compare it with each value in the column to get the result.

SELECT  lastname, annual_salary
FROM    employees
WHERE   annual_salary > (SELECT AVG(annual_salary)
				 FROM employees);

Do you see anything interesting?

There is a record in our table with a NULL value in annual_salary, but our query didn’t throw an error. This is because the SQL AVG() function ignores NULLs and simply calculates the average of the other records with numeric values. That is, it ignores the value from the row shown below.

AVG() With a DISTINCT Clause

Imagine you have some duplicated data in your tables, and you want to ignore duplicate values when calculating the average.

To do this in SQL, you have to use a DISTINCT clause. You can see the annual_salary column in our employees table has values that repeat, for instance, 5000 and 10000. If we want SQL to consider these values only once, we use AVG() with a DISTINCT clause.

This is how the results differ:

In the first case, the value 10,000 was included three times, and the value 5,000 was included twice. In the second case, these values were counted only once each, hence the difference in the results.

Aggregate Functions

AVG() belongs to a class of functions known as aggregate functions. An aggregate function returns a single computed result over multiple rows.

These functions are extremely important for analysis. It is often impossible to go through each record to gather insights from a table that may contain millions of rows. So, you aggregate these millions of rows into meaningful groups.

For instance, say you work at Facebook, and you want to find out which age groups spend the most time on your app. In this case, you create groups/buckets of different age groups then find the average time spent for each bucket.

Here are some of the other aggregate functions, each with an example use case:

If it’s not very clear to you right now, don’t worry. I have some good SQL AVG() examples next.

The SQL AVG() Function With a GROUP BY Clause

To demonstrate how AVG() works as an aggregate function in practice, let me take you through how you can use AVG() with a GROUP BY clause.

The SQL GROUP BY clause is used to group rows together. In most cases, a GROUP BY clause has one or more aggregate functions that calculate one or more metrics for the group.

Example 3

Imagine you work as an analyst in the compensation team of a company. You want to find out the average employee salary by department. To do so, you can use a GROUP BY clause with AVG() like this:

SELECT     department,
	      AVG(annual_salary)
FROM       employees
GROUP BY   department;

Here’s the result of this query:

It groups together all the records by department then computes the average annual_salary for each department.

Example 4

If you work in a large company with many departments, you may want to focus on the departments whose average salary is higher than a specific value. To do this, you need to filter out some groups from the result of your query.

Let’s say you want to find the departments whose average salaries exceed 10000. Your query looks like this:

SELECT     department,
	      AVG(annual_salary)
FROM       employees
GROUP BY   department
HAVING     AVG(annual_salary) > 10000;

Here’s the result of this query:

Since only the Finance department has the average salary greater than 10,000, it is the only row returned.

For more on how to filter records with aggregate functions, read this article.

You don’t need to have AVG() in the SELECT statement to use it in a HAVING clause. For example, the following query gives you just the name of the department in the result:

SELECT      department
FROM        employees
GROUP BY    department
HAVING      AVG(annual_salary) > 10000;

And the result:

The SQL AVG() Function With a CASE Statement

You can also use AVG() with a CASE statement. If you're not familiar with CASE, take a look at this article.

Example 5

Let’s say you want to display “High” as the category when the average salary is greater than 7,000, and “Low” if it is equal or less. This is what the query looks like:

SELECT department,
	   CASE WHEN AVG(annual_salary) > 7000 THEN 'High'
	   ELSE 'Low' END as category
FROM employees
GROUP BY department;

The result of this query:

The average is computed for each department then compared with 7,000. If the average salary of a department is higher than 7,000, then 'High' is returned as its category.

When Not to Use AVG()

Although useful, the average has limitations as a metric. This is especially true when the values in your data set are very skewed.

For example, say you run a YouTube channel, and you have uploaded 20 videos so far. One video has hit a million views, but the rest has yet to see any traction. While it is technically possible to compute the average, in reality, only one video contributes to the average of 200,000.

When values are very skewed, the median is often a better metric. The median is the value at the 50th percentile of a series; that is, it is the point at which half the values in the series/group are below and half are above.

Ready to Use the SQL AVG() Function in Your Queries?

By now, you should have a good understanding of the SQL AVG() function and the various ways to use it. If you are just starting out and want a career that deals with data, SQL is a must-learn tool.

To learn more about standard functions like AVG(), I recommend this course by LearnSQL.com. The course is wonderfully designed and reinforces learning throughout to help you retain what you learn. If you are super enthusiastic to learn SQL end to end, then check out the SQL A to Z track.

The keys to becoming skilled at SQL are practicing and always staying updated with the developments and the new features. So, keep reading articles from LearnSQL.com for more and the latest in SQL.

Happy learning!