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.
863 total views, 1 views today