Black friday

Save big!

All courses under $5 - for a limited time!

Code has been added to clipboard!

Using the SQL Average Function

Reading time 2 min
Published Aug 9, 2017
Updated Oct 3, 2019

What is the SQL Average?

SQL AVG() is one of the five aggregate functions. It allows you to find out the average of a set of selected numerical values.

Syntax Requirements

To find out an average in SQL, you need to select a set of values the average will represent. Therefore, you need to start with the SELECT statement:

Example
SELECT AVG(col_name) FROM tbl_name

The parameters you need to define are simple:

  • col_name is the name of the column
  • tbl_name is the name of the table

Using SQL Average Function: An Example

To get a better idea on what is an average in SQL and how you can work with it, we will see a simple example using a demo database and a few code examples for you to try out.

Theory is great, but we recommend digging deeper!

A Demo Database

In the example below, you can see a table called Products:

ID Name Supplier_ID Category_ID Unit Price Unit_in_stock Unit_on_order
1 Cucumber 3 3 5 crates 19 17 16
2 Tomato 3 3 6 crates 1 24 Null
3 Cheese 3 4 5 kg boxes 11 11 6
4 Milk 3 3 5 L bottles 22 56 12
5 Bread 3 3 36 boxes 26 26 1

Counting the Average

Using the statement below, you can calculate the average price of those listed in the table:

Example
SELECT AVG(Price) AS PriceAverageID FROM Products;

Working With the Average

Now, in the code example below, you can see how to only pick out the values that are higher than the average in SQL. We also choose two columns to be displayed – Name and Price:

Example
SELECT Name, Price FROM Products WHERE Price>(SELECT AVG(Price) FROM Products);