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 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

SELECT AVG(EMP_SALARY) AS AVG_SAL FROM dbo.EMP

Lets understand how does AVG function calculates the average

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

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'

 

You can also see the other Aggregate functions.

SQL MAX Function

SQL COUNT Function

SQL MIN Function

SQL SUM Function

 863 total views,  1 views today