[thelist] case statement catch-all? MSSQL

[Joshua Olson]

> -----Original Message-----
> From: Brian Cummiskey
> Sent: Wednesday, August 24, 2005 11:32 AM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] case statement catch-all? MSSQL
> 
> 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

Brian,

The results, as you have coded the query, will come back spanning three rows
because of the GROUP BY clause. 

Drop the group by, and you should be closer to what you want:

SELECT
	SUM(case site when 'B' then 1 else 0 end) AS 'Bristol, CT',	
	SUM(case site when 'L' then 1 else 0 end) AS 'Lock Haven, PA',
	SUM(case site when 'T' then 1 else 0 end) AS 'Brownsville, TX',
	SUM(case WHEN site IN ('B', 'L', 'T') then 1 else 0 end) AS 'ALL'
from table

Also, adding null to anything is null, so replace null with 0.

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/