1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table #budgets
CREATE TABLE #budgets
(
dept tinyint IDENTITY,
current_year decimal NULL,
previous_year decimal NULL
);
INSERT #budgets VALUES(100000, 150000);
INSERT #budgets VALUES(NULL, 300000);
INSERT #budgets VALUES(0, 100000);
INSERT #budgets VALUES(NULL, 150000);
INSERT #budgets VALUES(300000, 250000);
GO
SET NOCOUNT OFF;
SELECT avg(nullif(coalesce(current_year,
previous_year),0)) AS 'Average Budget'
FROM #budgets;
Average Budget 212500.000000
Explanation: 850000 / 4 = 212500. We divide by 4 and not 5 because one of the row has a NULL value.