[thelist] case statement catch-all? MSSQL

[Jay Blanchard]

[snip]
I re-wrote it to work like this:
SELECT
	SUM(case site when 'B' then 1 else null end) AS 'Bristol, CT',	
	SUM(case site when 'L' then 1 else null end) AS 'Lock Haven,
PA',
	SUM(case site when 'T' then 1 else null end) AS 'Brownsville,
TX',
	SUM(case site when 'B' then 1 when 'L' then 1 when 'T' then 1
else null 
end) AS 'ALL'
from table
group by site

this works, syntatcially, but it returns the results like this:

Bristol	LockHaven	Brownsville	All
------------------------------------------------
5	NULL		NULL		5
NULL	2		NULL		2
NULL	NULL		7		7


not what i'm after...  i need a grand total, 1 line result like:


Bristol	LockHaven	Brownsville	All
------------------------------------------------
5	2		7		14
[/snip]

At this point you may have to use your programming/scripting language to
finish this up because doing it in the query may be nearly impossible
since you have to choose a 'group by' parameter to make the math work.