How to Find the Average of a Numeric Column in SQL

Database:

Operators:

Table of Contents

Problem
Example
Solution
Discussion
- Find Average for Groups
- Using AVG with ROUND

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.

id	city	year	amount
1	Los Angeles	2017	2345.50
2	Chicago	2018	1345.46
3	Annandale	2016	900.56
4	Annandale	2017	23230.22
5	Los Angeles	2018	12456.20
6	Chicago	2017	89000.40
7	Annandale	2018	21005.77
8	Chicago	2016	2300.89

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 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:

city	amount
Los Angeles	7400.850000
Chicago	30882.250000
Annandale	15045.516667

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;

Problem

Example

Solution

Discussion

Find Average for Groups

Using AVG with ROUND

Recommended courses:

Recommended articles:

See also: