{"id":147,"date":"2016-03-24T09:22:59","date_gmt":"2016-03-24T09:22:59","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=147"},"modified":"2025-01-18T20:34:34","modified_gmt":"2025-01-19T03:34:34","slug":"sql-avg","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-aggregate-functions\/sql-avg\/","title":{"rendered":"SQL AVG Function"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to use SQL AVG<\/code> function to calculate the average value of a set of values.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQL AVG function #<\/a><\/h2>\r\n\r\n\r\n\r\n

The AVG<\/code> function is an aggregate function<\/a> that calculates the average value of a set.<\/p>\r\n\r\n\r\n\r\n

Here’s the syntax of the AVG<\/code> function:<\/p>\r\n\r\n\r\n

AVG([ALL|DISTINCT] expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Suppose you use the AVG()<\/code> function to calculate the average value of the set of (1, 2, 3, 3, 4), the AVG<\/code> function will perform the following calculation:<\/p>\r\n\r\n\r\n
(1+2+3+3+4)\/5 = 2.6<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
However, the AVG(DISTINCT)<\/code> will calculate the average value of the distinct values:<\/p>\r\n\r\n\r\n
(1+2+3+4)\/4 = 2.5<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SQL AVG function examples #<\/a><\/h2>\r\n\r\n\r\n\r\n
We will use the employees<\/code> table in the sample database<\/a> to demonstrate how the AVG<\/code> function works:<\/p>\r\n\r\n\r\n\r\n
\"employees_table\"<\/figure>\r\n\r\n\r\n\r\n
To calculate the average salary of all employees, you apply the AVG<\/code> function to the salary column as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  AVG<\/span>(salary) average_salary\r\nFROM<\/span>\r\n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    average_salary<\/span>\r\n-----------------------<\/span>\r\n 8060.0000000000000000<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SELECT<\/span>\r\n  ROUND<\/span>(AVG<\/span>(DISTINCT<\/span> salary), 2<\/span>) average_salary\r\nFROM<\/span>\r\n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Output:<\/p>\r\n\r\n\r\n
 average_salary<\/span>\r\n----------------<\/span>\r\n        7845.45<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
It changed because some employees have the same salary.<\/p>\r\n\r\n\r\n\r\n
SELECT<\/span> \r\n  ROUND<\/span>(AVG<\/span>(DISTINCT<\/span> salary), 2<\/span>) average_salary\r\nFROM<\/span>\r\n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 average_salary<\/span>\r\n----------------<\/span>\r\n        7845.45<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
For instance, to calculate the average salary of employees in the department id 5, you use the following query:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  ROUND<\/span>(AVG<\/span>(salary), 2<\/span>) average_salary\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  department_id = 5<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 average_salary<\/span>\r\n----------------<\/span>\r\n        5885.71<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The following statement returns the average salary of employees who hold the job id 6:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  ROUND<\/span>(AVG<\/span>(salary), 2<\/span>) average_salary\r\nFROM<\/span>\r\n  employees\r\nWHERE<\/span>\r\n  job_id = 6<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 average_salary<\/span>\r\n----------------<\/span>\r\n        7920.00<\/span>\r\n(1 row<\/span>)<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Using AVG function with GROUP BY clause example #<\/a><\/h3>\r\n\r\n\r\n\r\n
To calculate the average values of groups, we use the AVG<\/code> function with the GROUP BY<\/a> clause.<\/p>\r\n\r\n\r\n\r\n
For example, the following statement returns the departments and the average salary of employees of each department:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  department_id,\r\n  ROUND<\/span>(AVG<\/span>(salary), 2<\/span>) average_salary\r\nFROM<\/span>\r\n  employees\r\nGROUP<\/span> BY<\/span>\r\n  department_id\r\nORDER<\/span> BY<\/span>\r\n  average_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 department_id | average_salary\r\n---------------+----------------\r\n             3 |        4150.00\r\n             1 |        4400.00\r\n             6 |        5760.00\r\n             5 |        5885.71\r\n             4 |        6500.00\r\n            10 |        8600.00\r\n             2 |        9500.00\r\n             8 |        9616.67\r\n             7 |       10000.00\r\n            11 |       10150.00\r\n             9 |       19333.33<\/code><\/span><\/pre>\r\n\r\n\r\n
We can use the inner join<\/a> clause to join the employees<\/code> table with the departments<\/code> table to get the department name data:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  department_name,\r\n  ROUND<\/span>(AVG<\/span>(salary), 2<\/span>) average_salary\r\nFROM<\/span>\r\n  employees e\r\n  INNER<\/span> JOIN<\/span> departments d ON<\/span> d.department_id = e.department_id\r\nGROUP<\/span> BY<\/span>\r\n  department_name\r\nORDER<\/span> BY<\/span>\r\n  average_salary;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 department_name  | average_salary\r\n------------------+----------------\r\n Purchasing       |        4150.00<\/span>\r\n Administration   |        4400.00<\/span>\r\n IT               |        5760.00<\/span>\r\n Shipping         |        5885.71<\/span>\r\n Human Resources  |        6500.00<\/span>\r\n Finance          |        8600.00<\/span>\r\n Marketing        |        9500.00<\/span>\r\n Sales            |        9616.67<\/span>\r\n Public<\/span> Relations |       10000.00<\/span>\r\n Accounting       |       10150.00<\/span>\r\n Executive        |       19333.33<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Using AVG function with HAVING clause example #<\/a><\/h3>\r\n\r\n\r\n\r\n
To filter groups, you use the AVG<\/code> function in the HAVING<\/a> clause.<\/p>\r\n\r\n\r\n\r\n
For example, the following statement retrieves departments that have an average salary of less than 5000<\/code>:<\/p>\r\n\r\n\r\n
SELECT\r\n  department_name,\r\n  ROUND(AVG(salary), 2) average_salary\r\nFROM\r\n  employees e\r\n  INNER JOIN departments d ON d.department_id = e.department_id\r\nGROUP BY\r\n  department_name\r\nHAVING AVG(salary) < 5000\r\nORDER BY\r\n  average_salary DESC;<\/code><\/span><\/pre>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
 department_name | average_salary\r\n-----------------+----------------\r\n Administration  |        4400.00\r\n Purchasing      |        4150.00<\/code><\/span><\/pre>\r\n\r\n\r\n
Using SQL AVG function with a subquery #<\/a><\/h3>\r\n\r\n\r\n\r\n
You can apply AVG<\/code> function multiple times in a single SQL statement to calculate the average value of a set of average values.<\/p>\r\n\r\n\r\n\r\n
For example, we can use the AVG<\/code> function to calculate the average salary of employees in each department and apply the AVG<\/code> function one more time to calculate the average salary of departments:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n  ROUND<\/span>(AVG<\/span>(average_salary_department), 2<\/span>) average_salary\r\nFROM<\/span>\r\n  (\r\n    SELECT<\/span>\r\n      AVG<\/span>(salary) average_salary_department\r\n    FROM<\/span>\r\n      employees\r\n    GROUP<\/span> BY<\/span>\r\n      department_id\r\n  ) t;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
 average_salary<\/span>\r\n----------------<\/span>\r\n        8535.97<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
How the query works.<\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
To calculate the average value of a subset of values, we add a WHERE clause<\/a> to the SELECT statement<\/a>.<\/p>\r\n\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
To round the result to 2 decimal places, you use the ROUND<\/a><\/code> function as follows:<\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n\r\n
Let’s apply the DISTINCT<\/a> operator to see if the result changes:<\/p>\r\n\r\n\r\n
Try it<\/a><\/p>\r\n\r\n\r\n
If you use the ALL<\/code> keyword, the AVG<\/code> function calculates the average value of all values, including duplicates. If you use the DISTINCT<\/a> keyword, the AVG<\/code> function calculates the average value of distinct values. The AVG<\/code> function uses ALL<\/code> by default.<\/p>\r\n\r\n\r\n\r\n