How to Find the Average of a Numeric Column in SQL

Problem

You’d like to calculate the average of numbers in a given column.

Example

Our database has a table named sale with the following columns: id, city, year, and amount. We want to calculate the average sales, regardless of city or year.

Solution

SELECT AVG(amount) as avg_amount
FROM sale;

Here’s the result:

Discussion

If you’d like to calculate the average of numeric values stored in a column, you can do so using the AVG() aggregate function. It takes one argument: the expression whose average you want to calculate. If you haven’t specified any other columns in the SELECT clause, the average will be calculated for all records in the table.

Find Average for Groups

Of course, since it’s an aggregate function, AVG() can also be used with groups. For example, if we’d like to find the average sale per city, we can write this query:

SELECT city, AVG(amount) as avg_amount
FROM sale
GROUP BY city;

The average is calculated for each city:

Using AVG with ROUND

Additionally, if the average has to be rounded, you can pass the result of the AVG() function into ROUND():

SELECT city, 
  ROUND(AVG(amount), 2) as avg_amount
FROM sale
GROUP BY city;