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()ignoresNULLvalues.- It works only with number columns.
In the exercise, find the average age in one department and round it.