Skip to content
Home » SQL Server AVG

SQL Server AVG

SQL Server AVG function is an aggregate function that returns the average of the values.




It calculates the average of values by dividing the total of these values by the number of values except for the NULL values .

SYNTAX

AVG (expression)

Lets look at an example of using AVG() function in SQL Server.

First, we create a sample table named as EMP and insert some records into this table.

CREATE TABLE dbo.EMP ( 
EMPID INT NOT NULL, 
EMP_DEPT VARCHAR(50), 
EMP_NAME VARCHAR(50), 
EMP_SALARY NUMERIC(9,2)
)

 INSERT INTO dbo.EMP
(EMPID, EMP_DEPT, EMP_NAME, EMP_SALARY) 
VALUES
(101, 'PRODUCTION', 'RAJAT M',75000.00),
(102, 'PRODUCTION', 'MUKESH BHATIA',70000.00),
(103, 'PRODUCTION', 'MUSKAN MEHTA',75000.00),
(104, 'SALES', 'ROHAN B NARAYA',45000.00),
(105, 'SALES', 'SUSHIL DAS',40000.00),
(106, 'SALES', 'MANISH',45000.00),
(107, 'PRODUCTION', 'RAJESH SINGH',78000.00),
(108, 'HR', 'MOHIN KHAN',50000.00),
(109, 'HR', 'SUSHANT K SINGH',55000.00),
(110, 'HR', 'LAKSHYA RAWAT',55000.00),
(111, 'PRODUCTION', 'MANOJ KUMAR',75000.00),
(112, 'SALES', 'SUJOY M',40000.00),
(113, 'LOGISTIC', 'VINAY AGARWAL',35000.00),
(114, 'LOGISTIC','MUSTAKIM M',35000.00),
(115, 'LOGISTIC', 'VIJAY KUMAWAT',45000.00)

To get the average salary of all employees in the entire EMP table

Following statement return the average salary of all employees from table EMP.

SELECT AVG(EMP_SALARY) AS AVG_SAL FROM dbo.EMP

Lets understand the working of AVG function, it calculates the average by dividing the total of all values for EMP_SALARY by the number of EMP_SALARY, see below T-SQL statement that is equivalent to AVG function, which gives the same result.

SELECT SUM(EMP_SALARY)/COUNT(EMP_SALARY) AS AVG_SAL FROM dbo.EMP

Get the average salary by department

Following statement return the average salary by department.

SELECT EMP_DEPT, AVG(EMP_SALARY) AS AVG_SAL FROM dbo.EMP
GROUP BY EMP_DEPT

Get the Average salary of Employees for PRODUCTION department

SELECT AVG(EMP_SALARY) AS AVG_SAL FROM dbo.EMP

WHERE EMP_DEPT = 'PRODUCTION'

Also Read..

Aggregate functions

SQL MAX Function

SQL COUNT Function

SQL MIN Function

SQL SUM Function

 

Loading

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.