SQL AVG Function

Summary: in this tutorial, you will learn how to use SQL AVG function to calculate the average value of a set of values.

Introduction to SQL AVG function #

The AVG function is an aggregate function that calculates the average value of a set.

Here’s the syntax of the AVG function:

AVG([ALL|DISTINCT] expression)Code language: SQL (Structured Query Language) (sql)

If you use the ALL keyword, the AVG function calculates the average value of all values, including duplicates. If you use the DISTINCT keyword, the AVG function calculates the average value of distinct values. The AVG function uses ALL by default.

Suppose you use the AVG() function to calculate the average value of the set of (1, 2, 3, 3, 4), the AVG function will perform the following calculation:

(1+2+3+3+4)/5 = 2.6Code language: SQL (Structured Query Language) (sql)

However, the AVG(DISTINCT) will calculate the average value of the distinct values:

(1+2+3+4)/4 = 2.5Code language: SQL (Structured Query Language) (sql)

SQL AVG function examples #

We will use the employees table in the sample database to demonstrate how the AVG function works:

To calculate the average salary of all employees, you apply the AVG function to the salary column as follows:

SELECT
  AVG(salary) average_salary
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

    average_salary
-----------------------
 8060.0000000000000000Code language: CSS (css)

Let’s apply the DISTINCT operator to see if the result changes:

SELECT
  ROUND(AVG(DISTINCT salary), 2) average_salary
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 average_salary
----------------
        7845.45Code language: CSS (css)

It changed because some employees have the same salary.

To round the result to 2 decimal places, you use the ROUND function as follows:

SELECT 
  ROUND(AVG(DISTINCT salary), 2) average_salary
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

 average_salary
----------------
        7845.45Code language: CSS (css)

To calculate the average value of a subset of values, we add a WHERE clause to the SELECT statement.

For instance, to calculate the average salary of employees in the department id 5, you use the following query:

SELECT
  ROUND(AVG(salary), 2) average_salary
FROM
  employees
WHERE
  department_id = 5;Code language: SQL (Structured Query Language) (sql)

Try it

 average_salary
----------------
        5885.71Code language: CSS (css)

The following statement returns the average salary of employees who hold the job id 6:

SELECT
  ROUND(AVG(salary), 2) average_salary
FROM
  employees
WHERE
  job_id = 6;Code language: SQL (Structured Query Language) (sql)

Try it

 average_salary
----------------
        7920.00
(1 row)Code language: CSS (css)

Using AVG function with GROUP BY clause example #

To calculate the average values of groups, we use the AVG function with the GROUP BY clause.

For example, the following statement returns the departments and the average salary of employees of each department:

SELECT
  department_id,
  ROUND(AVG(salary), 2) average_salary
FROM
  employees
GROUP BY
  department_id
ORDER BY
  average_salary;Code language: SQL (Structured Query Language) (sql)

Try it

 department_id | average_salary
---------------+----------------
             3 |        4150.00
             1 |        4400.00
             6 |        5760.00
             5 |        5885.71
             4 |        6500.00
            10 |        8600.00
             2 |        9500.00
             8 |        9616.67
             7 |       10000.00
            11 |       10150.00
             9 |       19333.33

We can use the inner join clause to join the employees table with the departments table to get the department name data:

SELECT
  department_name,
  ROUND(AVG(salary), 2) average_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
ORDER BY
  average_salary;Code language: SQL (Structured Query Language) (sql)

Try it

 department_name  | average_salary
------------------+----------------
 Purchasing       |        4150.00
 Administration   |        4400.00
 IT               |        5760.00
 Shipping         |        5885.71
 Human Resources  |        6500.00
 Finance          |        8600.00
 Marketing        |        9500.00
 Sales            |        9616.67
 Public Relations |       10000.00
 Accounting       |       10150.00
 Executive        |       19333.33Code language: PHP (php)

Using AVG function with HAVING clause example #

To filter groups, you use the AVG function in the HAVING clause.

For example, the following statement retrieves departments that have an average salary of less than 5000:

SELECT
  department_name,
  ROUND(AVG(salary), 2) average_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
  department_name
HAVING AVG(salary) < 5000
ORDER BY
  average_salary DESC;

Try it

 department_name | average_salary
-----------------+----------------
 Administration  |        4400.00
 Purchasing      |        4150.00

Using SQL AVG function with a subquery #

You can apply AVG function multiple times in a single SQL statement to calculate the average value of a set of average values.

For example, we can use the AVG function to calculate the average salary of employees in each department and apply the AVG function one more time to calculate the average salary of departments:

SELECT
  ROUND(AVG(average_salary_department), 2) average_salary
FROM
  (
    SELECT
      AVG(salary) average_salary_department
    FROM
      employees
    GROUP BY
      department_id
  ) t;Code language: SQL (Structured Query Language) (sql)

Try it

 average_salary
----------------
        8535.97Code language: CSS (css)

How the query works.

The subquery returns a set of the average salaries of employees for each department.
The outer query returns the average salary of departments.

Summary #

Use the AVG function to calculate the average value of a set.
Use the AVG function with the GROUP BY clause to calculate average values for each group.

Databases #

Was this tutorial helpful ?