🚨 Get Your Free NFT Certificate Mint by Completing the Web3 Exam! START NOW

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.

DataCamp
Pros
  • Easy to use with a learn-by-doing approach
  • Offers quality content
  • Gamified in-browser coding experience
  • The price matches the quality
  • Suitable for learners ranging from beginner to advanced
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable
Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid Certificates of completion
Udemy
Pros
  • Easy to navigate
  • No technical issues
  • Seems to care about its users
Main Features
  • Huge variety of courses
  • 30-day refund policy
  • Free certificates of completion

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);