sql averageSQL AVG returns a calculated average result based on values in a table column. AVG is one of many SQL aggregate functions that allow the programmer to apply mathematic operations to expressions or sets of expressions. It differs primarily from an operator due to its syntax. Operators perform mathematic operations on two or more columns, but aggregate functions let you perform these operations using internal SQL methods.

Are you a beginner in SQL and want to learn more? Take a class at Udemy.com

SQL AVG

The syntax for the SQL AVG function for MySQL, PostgeSQL and SQL Server is

AVG([ALL|DISTINCT]expression1, expression2, expression3 ….)

The syntax for DB2 & Oracle is

AVG([ALL|DISTINCT] expression1, expression2, expression3..) OVER (windows_clause)

Parameters

ALL = Applies to all values

DISTINCT = Applies to only unique values

Expression = Argument made up of a single variable, constant, scalar function or column name

Typical usage

SELECT AVG (Column/Field) AS (New Field) FROM (Table)

The next section will run through some examples of SQL AVG in real world scenarios with suggested code as examples. All the examples will use the table below for reference and values.

Sample Database Table for examples that follow

ProductID ProductName SupplierID CategoryID Vendor_ID Price
1 Frozen Strawberry 1 1 101 18
2 Blueberry Juice 1 1 102 19
3 Maple Syrup 1 2 101 10
4 Plain Yoghurt 2 2 107 21.35
5 Sugar 2 2 102 25

 

Example 1

In this example, the select AVG() function calculates the average in the column Price.

SELECT AVG(Price) AS PriceAverage FROM Products;

PriceAverage
18.67

 

Explanation: In this example, the AVG function calculates the average in the Price column in the Table Products under the heading PriceAverage.

Example 2

In this example, the aim is to find the products priced above the average.

SELECT ProductName, Price FROM Products

WHERE Price>(SELECT AVG(Price) FROM Products);

ProductName Price
Blueberry Juice 19
Plain Yoghurt 21.35
Sugar 25

 

Explanation – In this example the AVG function first calculates the average price for the column Price in the table Products. It selects those values that are greater (>) than the average value and lists them under ProductName and Price.

Example 3

In this scenario, the average of product prices between two limits is calculated.

Using SQL AVG () with a “where” clause:

SELECT AVG( AvgPrice)

FROM Products

WHERE Price>10 AND Price <= 25;

AvgPrice
19.45

 

Explanation – the where clause selects the values between 10 and an upper limit of less than or equal to 25. Avg() then finds the mean of the prices in that window.

 

Example 4

SQL AVG() with SUM()

This example works with the SUM() the object and adds all the values in the column Price and then uses the AVG function to calculate the average.

SELECT SUM(Price), AVG(Price)

FROM Products;

SUM(Price) AVG Price
93.35 18.67

 

SQL AVG with CAST()

The AVG() function returns an average calculation performed on a set of expressions with default decimal places. The CAST() function however can be used in conjunction with AVG() to increase or decrease the number of decimal places. When converting decimal and numeric data types, CAST() is better at preserving the decimal places. The AS-DECIMAL is used along with the specified type when using the CAST() to stipulate the decimal places.

Syntax

CAST (expression)

AS [Data Type] [specified format]

Parameters

Expression – Expression made up of a single constant, variable, scalar function, or column   name

Data Type – CHAR(), VARCHAR(), DECIMAL(), FLOAT

Specified Format – The required specified format

Using the Cast function with AVG()

Example 5

SELECT CAST(AVG(Price) AS DECIMAL(10,2))

FROM Products

Price
18.70

 

SQL AVG can use CAST both inside and outside of the AVG() function. Here is an example of each.

SQL AVG() using CAST() inside the AVG()

Example 6

SELECT Vendor_ID, COUNT(*),

AVG(CAST(Price AS DECIMAL(12,2))) AS AvgPrice

FROM Products

GROUP BY Vendor_ID;

Vendor_ID Count  AvgPrice
101 2 14
102 2 22
107 1 21.35

 

Explanation – First, you need to retrieve and count the number of Vendor_Ids.  Then, you average the corresponding prices grouped by Vendor_ID. This will return an average price to two decimal places for each Vendor ID.

 

Averaging Date / Time Stamps in SQL

Another common usage for the AVG function is when you need to average dates and times. This can be more difficult to perform, but it can be very useful when doing trend analysis. One way SQL can do this is to use CAST() to convert the date/time stamps into floating point numbers.

The other way is to use the AVG() function as in this code snippet:

SELECT AVG (des_date – ord_date) AS average_despatch_days

FROM despatch

 

To learn more advanced PL/SQL concepts, sign up for this Udemy.com course

 

Using SQL AVG and ROUND()

SQL AVG can also work in conjunction with the ROUND() function to round a number to the specified number of places.

Example 8

 

SELECT ROUND(AVG(Price) ,0) AS “Rounded Price Avg.”

FROM Products;

Rounded Price Avg
19

 

Example 9

SQL AVG() with GROUP BY

SELECT Vendor_ID, AVG(Price)

FROM Products GROUP BY Vendor_ID;

Vendor_ID  AVG(Price)
101 14
102 22
107 21.35

 

Example 10

SQL AVG() with ROUND() and GROUP BY

Vendor_ID  AVG(Price)
101 14
102 22
107 21

 

Example 11

SQL AVG() with ROUND(), rounded to nearest negative value

SQL AVG and ROUND() can be used to round up to a specific decimal point. Rounding can be done from the nearest negative or positive place from the decimal point.

For instance, to get the average of ‘(534.224/2)’ with a heading ‘AVG’ and the average of ‘(534.224/2)’ rounded down to the first number to the left of the decimal point (-1), you can use the following code:

SELECT AVG (534.224/2) AS “Avg.”,

ROUND (AVG(534.224/2),-1) AS “Rounded -1 Decimal Place”

FROM Products;

AVG  Rounded -1 Decimal Place
267.112 267

 

The AVG () function in SQL is one of the aggregate functions that enables programmers to utilize built-in mathematical tools. They can be used alongside other functions such as sum, case and round to quickly calculate values. Aggregate functions bring mathematic capabilities to queries, making the programming more efficient and easier to produce.

 

To learn more about SQL and how to work with aggregates, take a class at Udemy.com

 

Top courses in SQL

SQL and Data Visualization - The Complete Bootcamp
Raffi Sarkissian | SQL | PostgreSQL | Metabase
4.7 (15)
SQL Basics for Beginners
Aslam Tayyab
4.5 (54)
Highest Rated
The Complete SQL Bootcamp 2020: Go from Zero to Hero
Jose Portilla
4.7 (73,835)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (43,052)
Bestseller
SQL for Data Analysis: Weekender Crash Course for Beginners
A Course You'll Actually Finish, David Kim, Peter Sefton
4.4 (11,267)
Bestseller
Master SQL For Data Science
Imtiaz Ahmad
4.6 (5,821)
SQL for Beginners: Learn SQL using MySQL and Database Design
Tim Buchalka's Learn Programming Academy, Jon Avis - SQL Instructor
4.4 (5,165)
Learn SQL +Security(pen) testing from Scratch
Rahul Shetty
4.5 (1,171)
Bestseller

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo