[thelist] case statement catch-all? MSSQL

[Brian Cummiskey]

Mark Rees wrote:

> Rewrite it like this for MSSQL
> 
> SUM(CASE WHEN CT='C' THEN 1 ELSE 0 END) AS 'CT',
> 


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