Might be worth considering placing a DEFAULT value of 0 on your column, otherwise you'll face the problem you know have.
Another approach might be to use COALESCE() like this
SET NOCOUNT ON
CREATE TABLE AveragingMultipleColumns
(year0 DECIMAL(8,5), year1 DECIMAL(8,5), year2 DECIMAL(8,5))
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(1,2,3)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(1,2,NULL)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(1,NULL,3)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(1,NULL,NULL)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(NULL,2,NULL)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(NULL,NULL,3)
INSERT INTO AveragingMultipleColumns (year0, year1,year2) VALUES(NULL,NULL,NULL)
SELECT year0, year1, year2,
CASE WHEN (year0 + year1 + year2) IS NOT NULL --most likely one first
THEN (year0 + year1 + year2)/3.0
WHEN (year0 + year1) IS NOT NULL
THEN (year0 + year1)/2.0
WHEN (year0 + year2) IS NOT NULL
THEN (year0 + year2)/2.0
WHEN (year1 + year2) IS NOT NULL
THEN (year1 + year1)/2.0
ELSE COALESCE (year0, year1, year2) END AS average --nulls and singletons
FROM AveragingMultipleColumns
DROP TABLE AveragingMultipleColumns
SET NOCOUNT OFF
year0 year1 year2 average
---------- ---------- ---------- ----------------
1.00000 2.00000 3.00000 2.00000000
1.00000 2.00000 NULL 1.50000000
1.00000 NULL 3.00000 2.00000000
1.00000 NULL NULL 1.00000000
NULL 2.00000 NULL 2.00000000
NULL NULL 3.00000 3.00000000
NULL NULL NULL NULL
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]