SQL AVG(): Calculate the Average of a Column

AVG returns the average (the arithmetic mean) of a column: the total divided by how many values there are. Average salary, average order value, average rating.

The example table#

Average of a column#

SELECT AVG(salary) AS avg_salary
FROM employees;

avg_salary
----------
63600

That is 318,000 ÷ 5. Depending on the database, you may see 63600.0000 or more decimal places.

Average per group#

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

department  | avg_salary
------------+-----------
Engineering | 79500
Sales       | 56500
Marketing   | 46000

Rounding the result#

Averages often have long decimals. ROUND(value, digits) tidies them:

SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

category    | avg_price
------------+----------
Electronics | 23166.67
Furniture   | 5750
Stationery  | 60

Electronics averages (55,000 + 2,500 + 12,000) ÷ 3 = 23,166.666…, rounded to 23,166.67.

Rows above the average#

You cannot write WHERE salary > AVG(salary), because aggregates are not allowed in WHERE. Calculate the average in a subquery instead:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

name  | salary
------+-------
Ravi  | 78000
Meera | 81000

The inner query runs first and returns 63,600; the outer query then keeps salaries above it.

AVG ignores NULLs#

This is the most important detail. AVG divides by the number of non-NULL values, not the number of rows:

SELECT AVG(manager_id) AS avg_mgr,
       SUM(manager_id) * 1.0 / COUNT(*) AS over_all_rows
FROM employees;

avg_mgr | over_all_rows
--------+--------------
4.33    | 2.6

AVG used (5 + 3 + 5) ÷ 3. Dividing by all 5 rows treats the two NULLs as zeros, which gives a different answer. Neither is wrong; decide which you mean. If missing values should count as zero, write AVG(COALESCE(column, 0)).

(manager_id is an id, so averaging it is only useful as an illustration. The same effect matters a lot for columns like rating or bonus.)

Integer averages#

In SQL Server, AVG of an integer column returns an integer, dropping the decimals: the average of 1 and 2 is 1. Convert first: AVG(CAST(score AS DECIMAL(10, 2))). PostgreSQL, MySQL and SQLite return decimals automatically.

Average vs median#

The average is pulled up or down by extreme values. If nine people earn 30,000 and one earns 3,000,000, the average is 327,000, which describes nobody. The median (the middle value) is often a better "typical" figure; PostgreSQL and Oracle compute it with PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary).

Common mistakes#

Practice#

SELECT ROUND(AVG(amount), 2) AS avg_order
FROM orders;

avg_order
---------
255

SELECT order_id, amount
FROM orders
WHERE amount < (SELECT AVG(amount) FROM orders);

order_id | amount
---------+-------
101      | 250
102      | 120
104      | 90

Key takeaway#