Tutorial Gateway

MySQL AVG Function

by

MySQL AVG is one of the Aggregate Functions, which is useful to calculate the average of total rows (or records) selected by the SELECT Statement. For example, If you want to find the Average Sales in your Area or to find the Average Cost of laptops present in your Store, then you can use the Avg function.

MySQL Avg Function Syntax

The basic syntax of the AVG in MySQL is as shown below:

SELECT AVG ([Column_Name])
FROM [Source]

For this finding Average in MySQL with an example, we are going to use the below-shown data

MySQL AVG Example 1

MySQL AVG Example

The Avg function in MySQL returns the average of total records present in the specified column. For example, The following query calculates the average of total number records present in Yearly_Income column from the customerdetails table.

-- AVG in MySQL Example
SELECT AVG(Yearly_Income) AS `Average Income`
FROM customerdetails;

OUTPUT

MySQL AVG Function Example 1

MySQL AVG Function Group By Example

In general, we use this Avg function to check for Average product price belongs to a particular color or category, etc. In this situation, we can use Group By Clause to group the products by color or category. Next, we use MySQL Average Function to find the average of each group. Let us see the Aggregate Function Example

-- MySQL AVG Function Example
USE company;
SELECT  Education,
        AVG(Yearly_Income)
FROM customerdetails
GROUP BY Education;

The above MySQL Average query group the Customers by their education, and finds the average income of each group (education qualification)

MySQL AVG Function Example 2

MySQL AVG Distinct Example

MySQL allows you to use the DISTINCT keyword along with the AVG function in SELECT Statement. AVG (DISTINCT Column_Name) calculates the average of the Unique number of records present in the table.

TIP: In MySQL, DISTINCT Keyword is used to remove the Duplicates from the specified column Name.

-- MySQL AVG Function Example
USE company;
SELECT  Education,
        AVG(DISTINCT Yearly_Income)
FROM customerdetails
GROUP BY Education;

The above AVG query find Unique number of records (by removing duplicates) present in Yearly_Income column, and then calculates the average of them

MySQL AVG Function Example 3