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
