SQL AVG

 

SQL AVG


Summary: In this tutorial, we will show you how to use the SQL AVG function to get the average value of a set.

Introduction to SQL AVG function

The SQL AVG function is an aggregate function that calculates the average value of a set. The following illustrates the syntax of the SQL AVG function:

AVG([ALL|DISTINCT] expression)

If we use the ALL keyword, the AVG function takes all values in the calculation. By default, the AVG function uses ALL whether we specify it or not.

If we specify the DISTINCT keyword explicitly, the AVG function will take the unique values only in the calculation.

For example, we have a set of (1,2,3,3,4) and apply the AVG(ALL) to this set, the AVG function will perform the following calculation:

(1+2+3+3+4)/5 = 2.6

However, the AVG(DISTINCT) will process as follows:

(1+2+3+4)/4 = 2.5

SQL AVG function examples

We will use the employees the table in the sample database to demonstrate how the SQL AVG function works. The following picture illustrates the structure of the employees table:

employees_table

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

SELECT AVG(salary) FROM employees;
SQL AVG example

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

SELECT AVG(DISTINCT salary) FROM employees;
SQL AVG DISTINCT example

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) FROM employees;
SQL AVG with ROUND function example

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, we use the following query:

SELECT AVG(DISTINCT salary) FROM employees WHERE department_id = 5;
SQL AVG WHERE example

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

SELECT AVG(salary) FROM employees WHERE job_id = 6;
SQL AVG WHERE job id

SQL AVG 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, AVG(salary) FROM employees GROUP BY department_id;
SQL AVG GROUP BY example

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

SELECT e.department_id, department_name, AVG(salary) FROM employees e INNER JOIN departments d ON d.department_id = e.department_id GROUP BY e.department_id;
SQL AVG with INNER JOIN example

SQL AVG with ORDER BY clause example

To sort the result set that includes the AVG results, you use the AVG function in the ORDER BY clause as follows:

SELECT
	e.department_id,
	department_name,
	AVG(salary)
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
	e.department_id
ORDER BY
	AVG(salary) DESC;
SQL AVG with ORDER BY example

SQL AVG with HAVING clause example

To filter group, you use the AVG function in the HAVING clause. For example, the following statement gets the department that has the average salary of less than 5000:

SQL AVG with HAVING clause example

SQL AVG with a subquery

We can apply the 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.

The following query illustrates the idea:

SELECT AVG(employee_sal_avg) FROM ( SELECT AVG(salary) employee_sal_avg FROM employees GROUP BY department_id ) t;

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.

In this tutorial, you have learned how to use the SQL AVG function to calculate the average value of a set.

Reactions

Post a Comment

0 Comments