AVG function SQL Server Example

SQL Server > Built-in Functions > AVG

AVG returns the average of the values in a group without Null values

Example:

1. Calculate average salary global and for each department

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (2,'p2', null,2)

select avg(Salary) avg_salary from #LocalTempTable

select avg(Salary) avg_salary, DeptId from #LocalTempTable group by DeptId

drop table #LocalTempTable

Results:

avg_salary
150

avg_salary DeptId
150            1
NULL       2

2. Average without zero (0)