Almost all engineers have used the AVG aggregate function, but have you ever checked whether the result is correct? This was discussed while I was conducting my new SQL class, here is an example for explaining it, where you might make a mistake;
1: CREATE TABLE dbo.EmployeeCommision
2: (
3: EmployeeId int PRIMARY KEY,
4: CommisionGiven money NULL
5: );
6:
7: -- Inserting five employees' commison records
8: -- In real implementation, two tables
9: -- will maintain for holding employees records
10: -- and commison records
11: INSERT INTO dbo.EmployeeCommison
12: (EmployeeId, CommisionGiven)
13: VALUES
14: (1, 50), (2, 50), (3, NULL), (4, 50), (5,50)
15:
16: -- Calculating the average commision given
17: -- to an employee, You may use LEFT OUTER JOIN
18: -- if two tables are maintained
19: SELECT AVG(CommisonGiven)
20: FROM dbo.EmployeeCommision
21: -- Result of above is 50, which is wrong in
22: -- this case to correct the issue, use
23: -- ISNULL function with AVG function
24:
25: SELECT AVG(ISNULL(CommisonGiven, 0))
26: FROM dbo.EmployeeCommision
27: -- Now the result is 40, which is correct.
The reason for this is because, all aggregate functions ignore NULL values. Only exception is COUNT(*) that counts NULL values.
