Skip to content
Related Articles

Related Articles

Improve Article

AVG() Function in SQL Server

  • Last Updated : 21 Jan, 2021

AVG() :

This function in SQL Server 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 one parameter.

  • expression – A 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.

CREATE TABLE product
(  
user_id int IDENTITY(100, 2) NOT NULL,    
product_1 VARCHAR(10),
product_2 VARCHAR(10),
price int  
);
INSERT product(product_1, price)  
VALUES ('rice', 400);

INSERT product(product_2, price)  
VALUES ('grains', 600);

SELECT AVG(price) FROM product;

Output :

500

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

Example-2 :



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

CREATE TABLE floats
(  
user_id int IDENTITY(100, 2) NOT NULL,
float_val float
);
INSERT floats(float_val)  
VALUES (3.5);

INSERT floats(float_val)  
VALUES (2.5);

SELECT AVG(float_val) FROM floats;

Output :

3

Example-3 :

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

CREATE TABLE package
(  
user_id int IDENTITY(100, 4) NOT NULL,  
item VARCHAR(10),
mrp int  
);
INSERT package(item, mrp)  
VALUES ('book1', 250);

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

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

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

Output :

  | user_id  | item     | mrp
--------------------------------
1 | 104      | book2    | 350
--------------------------------
2 | 108      | book3    | 400

Example-4 :

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

CREATE TABLE package
(  
user_id int IDENTITY(100, 4) NOT NULL,  
item VARCHAR(10),
mrp int,
sp int
);
INSERT package(item, mrp, sp)  
VALUES ('book1', 250, 240);

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

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

SELECT AVG(mrp-sp) FROM package;

Output :

30

Application :

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

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :