MySQL - AVG() Function

Quiz

Aggregation is generally defined as a consideration of a collection of objects that are bound together as a single entity; and MySQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit.

One such function is the MySQL AVG() function. This function is used to calculate the average of the values in a particular column/field. You must note the following points while working with AVG() function in MySQL −

The AVG() function only works on columns with numerical datatype. For any other datatype, the result-set will hold a '0' or NULL.
If the values don't exist in a table column, this function returns NULL.
If the column itself doesn't exist, an 'Unknown Column' error is displayed.

If you use the DISTINCT keyword, this function calculates and returns the average of only the unique values of the given column.

Syntax

Following is the syntax of the MySQL AVG() function −

AVG(expr);

Parameters

This method accepts only one parameter. The same is described below −

expr: The name of the column or expression for which we want to calculate the average.

Return value

This function returns the average value of the specified column or expression.

Example

Following is a simple example demonstrating the usage of the MySQL AVG() function. For that, let us first create a table named CUSTOMERS using the CREATE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below query adds 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

To verify whether the records are inserted, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Now, we are using the MySQL AVG() function to calculate the average salaries of all the customers −

SELECT AVG(SALARY) From CUSTOMERS;

Output

This will produce the following result −

AVG(SALARY)
5000.000000

Example

We can also use the MySQL AVG() function in subqueries to calculate the average salary of customers.

The following query retrieves all rows from the 'CUSTOMERS' table where the 'SALARY' is greater than the average salary of all customers −

SELECT * FROM CUSTOMERS 
WHERE SALARY > (SELECT AVG(SALARY) FROM CUSTOMERS);

Output

On executing the given query, the output is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

Example

A NULL value will be displayed as output, if the AVG() function is applied on non-existent rows.

Assume the previosly CUSTOMERS table, execute the below query to return the average salaries of the customers −

SELECT AVG(SALARY) FROM CUSTOMERS;

This will produce the following result −

AVG(SALARY)
5000.000000

Now, using the below query, we are retrieving the average marks of a customer named 'Rahul'. But the table holds no records of 'Rahul'. In this case, a NULL value will be returned.

SELECT AVG(SALARY) FROM CUSTOMERS WHERE NAME = 'Rahul';

Output

This will produce the NULL as result −

AVG(SALARY)
NULL

Print Page