AVG(): Calculate the Average

The AVG() function returns the average of a number column.
It is useful for things like average age or average price.

Syntax

SELECT AVG(column) FROM table_name WHERE condition;

Example 1: Average Age of All Employees

SELECT AVG(age) FROM employees;

Output:

         avg         
---------------------
 28.5000000000000000
(1 row)

PostgreSQL shows the average with many decimal places. It does this to keep the result exact.


Example 2: Round the Result with ROUND()

ROUND(value, n) rounds a number to n decimal places:

SELECT ROUND(AVG(age), 2) AS avg_age FROM employees;

Output:

 avg_age 
---------
   28.50
(1 row)

You will often use ROUND together with AVG.


Example 3: Average Age per Department

SELECT department, ROUND(AVG(age), 2) AS avg_age
FROM employees
GROUP BY department
ORDER BY department;

Output:

 department | avg_age 
------------+---------
 Finance    |   30.75
 HR         |   26.00
 IT         |   28.00
 Marketing  |   29.50
 Sales      |   28.00
(5 rows)

PostgreSQL Tip: Dividing Whole Numbers

When you divide one whole number by another, PostgreSQL gives a whole number and drops the rest. To get decimals, make one of the numbers a decimal, like 7.0:

SELECT 7 / 2 AS whole, 7.0 / 2 AS exact;

Output:

 whole |       exact        
-------+--------------------
     3 | 3.5000000000000000
(1 row)

AVG does not have this problem. It always returns the exact average.


Tip

  • AVG() ignores NULL values.
  • It works only with number columns.

In the exercise, find the average age in one department and round it.