AVG() Function in MySQL

AVG() function :

This function in MySQL is used to return the average value of the specified expression.

Features :

This function is used to find the average value of the specified expression.
This function comes under Numeric Functions.
This function accepts only one parameter namely expression.
This function ignores NULL values.

Syntax :

AVG(expression)

Parameter :

This method accepts only one parameter as follows.

expression –The specified numeric value may be either a stated field or a stated formula.

Returns :

It returns the average value of the specified expression.

Example-1 :

Using AVG() function and getting the output.

Creating table –

CREATE TABLE item13
(  
user_id int,    
product01 VARCHAR(4),
product02 VARCHAR(10),
price int  
);

Inserting data –

INSERT item13(product01, price)  
VALUES ('rice', 500);

INSERT item13(product02, price)  
VALUES ('grains', 700);

Reading Data –

SELECT AVG(price) FROM item13;

Output :

Here, the average of the first product’s price and the second product’s price is returned.

Example-2 :

Using AVG() function and getting the average of float values.

Creating table –

CREATE TABLE floats
(  
user_id int,
float_val float
);

Inserting Data –

INSERT floats(float_val)  
VALUES (3.5);

INSERT floats(float_val)  
VALUES (2.5);

Reading Data –

SELECT AVG(float_val) FROM floats;

Output :

Example-3 :

Using AVG() function and getting the output where MRP is greater than the average MRP of the products.

Creating table –

CREATE TABLE package01
(  
user_id int NOT NULL AUTO_INCREMENT,  
item VARCHAR(10),
mrp int, 
PRIMARY KEY(user_id)  
);

Inserting Data –

INSERT package01(item, mrp)  
VALUES ('book1', 250);

INSERT package01(item, mrp)  
VALUES ('book2', 350);

INSERT package01(item, mrp)  
VALUES ('book3', 400);

Reading Data –

SELECT * FROM package01
WHERE mrp > (SELECT AVG(mrp) FROM package01);

Output :

user_id	item	mrp
2	book2	350
3	book3	400

Example-4 :

Using AVG() function and getting the average of the (MRP-sales price).

Creating table –

CREATE TABLE package011
(  
user_id int NOT NULL AUTO_INCREMENT,  
item VARCHAR(10) NOT NULL,
mrp int NOT NULL,
sp int NOT NULL,
PRIMARY KEY(user_id)  
);

Inserting Data –

INSERT package011(item, mrp, sp)  
VALUES ('book1', 250, 240);

INSERT package011(item, mrp, sp)  
VALUES ('book2', 350, 320);

INSERT package011(item, mrp, sp)  
VALUES ('book3', 400, 350);

Reading Data –

SELECT AVG(mrp-sp) FROM package011;

Output :

Application :

This function is used to find the average of the expression specified.

Article Tags :

SQL

DBMS-SQL

mysql