Averages with AVG() — calculating averages by columns

If you’ve ever tried to figure out the average exam score or, say, the average salary in a department, then you’re already familiar with the idea of an arithmetic mean. Honestly, you probably learned this back in school. In SQL, any task that involves calculating an average value in a dataset is solved with the AVG() function.

The AVG() function is an aggregate function that calculates the arithmetic mean for a numeric column. It adds up all the values in the specified column and divides the result by the number of those values. It ignores NULL (and this ignoring, weirdly enough, actually makes life easier — but more on that later).

Syntax for AVG()

Let’s start with the basic syntax:

SELECT AVG(column)
FROM table;

Heads up: here, column is the column with the numeric values you want to average.

Example 1: Average employee salary

Imagine we have a table called employees that stores info about employees and their salaries:

Simple query to calculate the average salary:

SELECT AVG(salary)::integer AS average_salary
FROM employees;

The cast to integer is added for readability — without it, AVG(salary) over an integer column returns the numeric type: 54250.0000000000000000. Result:

How does this work?

• AVG() adds up all the salary values: 50000 + 60000 + 55000 + 52000 = 217000.
• It divides the sum by the number of non-null values: 217000 / 4 = 54250.

How AVG() works with NULL

You might have noticed that when calculating the average salary, the NULL value in the salary column was ignored. That’s a key feature of AVG(). It only considers non-NULL values.

Let’s try an example:

SELECT AVG(NULL) AS result;

Result:

This just proves again that AVG() ignores NULL. But if your whole dataset is NULL, the result will be NULL.

But if you have 0 instead of NULL in the table, that value won’t be ignored.

Table employees

SQL query:

SELECT AVG(salary) AS avg_salary
FROM employees;

Result:

Why is that?

Because AVG() will calculate:

[(1000 + 0 + 2000) / 3 = 1000]

The row with NULL is ignored when calculating the average.

Example: Calculating the average age of students

Now let’s look at the students table:

Query:

SELECT AVG(age) AS average_age
FROM students;

Result:

• AVG() ignores the student Maria, since her age is NULL.
• The average is calculated as: (20 + 22 + 21) / 3 = 21.

Rounding the result

Sometimes the result of AVG() is a decimal with a bunch of digits after the dot.

If you want a rounded number, you can use the ROUND() function.

Table employees

SQL query

SELECT ROUND(AVG(salary), 2) AS rounded_average_salary
FROM employees;

Result

The row with NULL is excluded from the calculation, so the average is based on three values.

Filtering data before calculating AVG()

If you want to calculate the average, but only for values that meet certain conditions, use WHERE.

Table employees

Example: Let’s find the average salary of employees whose id > 2.

SELECT AVG(salary) AS average_salary
FROM employees
WHERE id > 2;

Result

Only salaries with id = 3 and id = 4 are included in the calculation. The row with NULL is excluded.

Example: More complex queries with AVG()

You can combine the AVG() function with other aggregate functions and operators.

Let’s say we have a sales table called sales:

Query to calculate the average total sales amount:

SELECT ROUND(AVG(quantity * price), 2) AS average_total_sale
FROM sales;

Result:

Calculation: (2*500 + 1*1500 + 3*300) / 3 = (1000 + 1500 + 900) / 3 = 3400 / 3 ≈ 1133.33.

Real-life tips and common mistakes

Be careful when working with AVG() to avoid common mistakes:

NULL values: Sometimes it’s surprising why the result is unexpectedly lower than you thought. Remember, AVG() skips rows with NULL.

Mixing data types: If your column mixes numbers and text (which is bad practice anyway), AVG() will throw an error.