PostgreSQL – AVG (Average) Function

AVG function is an aggregate function that allows you to get the average of values.

Example:

Consider the employee table as shown below:

id	first_name	last_name	department	salary
101	bryan	walker	sales	50,000
102	alex	carpenter	accounts	55,000
103	sam	white	sales	60,000
104	john	barry	sales	54,000
105	bill	wood	accounts	65,000
106	robert	hopkins	support	70,000
107	david	lee	support	72,000
108	jack	sears	support	65,000
109	tom	young	sales	55,000

You can get the average of all salaries as shown below:

SELECT AVG(salary) FROM employee;

avg
------
60,667

You can use AVG along with the group by clause. Example: To return average of salaries per department, you can group by department as shown below:

SELECT department, AVG(salary) FROM employee GROUP BY department;

department  sum
----------  ---
sales       54,750
accounts    60,000
support     69,000

You can filter grouped results using HAVING clause. Example: Below query returns only the departments that are paying an average salary of more than 60,000 to employees.

SELECT department, SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>60000;

department  sum
----------  ------
support     69,000