Back to cookbooks list Articles Cookbook

How to Find the Average of a Numeric Column in SQL

  • AVG

Problem:

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

Example:

Our database has a table named sale with data in the following columns: id, city, year, and amount.

idcityyearamount
1Los Angeles20172345.50
2Chicago20181345.46
3Annandale2016900.56
4Annandale201723230.22
5Los Angeles201812456.20
6Chicago201789000.40
7Annandale201821005.77
8Chicago20162300.89

Let’s calculate the average sales, regardless of city or year.

Solution:

SELECT AVG(amount) as avg_amount
FROM sale;

Here’s the result:

avg_amount
19073.125000

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 as its argument the name of the column 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.

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:

cityamount
Los Angeles7400.850000
Chicago30882.250000
Annandale15045.516667

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;

Recommended courses:

Recommended articles:

See also: