case count query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

case count query

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-11-25 : 12:09:55

Hi all,
I wrote the following code at the bottom of my post which works BUT produces output like this:


AREA			      A1    B1   C1    D1
East Midlands		0	44	0	0
East Midlands		50	0	0	0
Eastern England		112	0	0	0
Eastern England		0	73	0	0
London			0	0	4	0
London			0	364	0	0
London			781	0	0	0
North East England	0	0	1	0
North East England	34	0	0	0
North East England	0	31	0	0
North West England	0	72	0	0
North West England	0	0	0	1
North West England	73	0	0	0
Northern Ireland	      7	0     0	0
Northern Ireland	      0	3     0	0
Northern Ireland	      0	0     1	0
Scotland		      0	0     1	0
Scotland		      36    0     0	0
Scotland		      0	29    0	0
South East England      0	246   0	0
South East England	385   0     0	0
South West England	0	 79	0	0
South West England	91	 0	0	0
Wales			      0	 13	0	0
Wales			      21 	0	0	0
West Midlands		0	55	0	0
West Midlands		67	0	0	0
Yorkshire & the Humber	0	47	0	0
Yorkshire & the Humber	50	0	0	0
Yorkshire & the Humber	0	0	2	0

I want to get rid of the area duplication and all the 0's so I end up with a single entry for each area and a total count for A1, B1, C1 and D1. Any help is appreciated!

SELECT  'Area' = CASE 
WHEN OA.AttributeId=78 THEN 'North East England'
WHEN OA.AttributeId=79 THEN 'North West England'
WHEN OA.AttributeId=80 THEN 'Yorkshire & the Humber'
WHEN OA.AttributeId=81 THEN 'East Midlands'
WHEN OA.AttributeId=82 THEN 'West Midlands'
WHEN OA.AttributeId=83 THEN 'Eastern England'
WHEN OA.AttributeId=84 THEN 'South East England'
WHEN OA.AttributeId=85 THEN 'South West England'
WHEN OA.AttributeId=86 THEN 'London'
WHEN OA.AttributeId=87 THEN 'Scotland'
WHEN OA.AttributeId=88 THEN 'Wales'
WHEN OA.AttributeId=89 THEN 'Northern Ireland'
END
,'A1' = CASE 
WHEN statusid='57' then count(statusid) else 0
end
,'B1' = CASE 
WHEN statusid='64' then count(statusid) else 0
end
,'C1' = CASE 
WHEN statusid='65' then count(statusid) else 0
end
,'D1' = CASE 
WHEN statusid='69' then count(statusid) else 0
end

FROM Applicants
INNER JOIN SO ON A.ApplicantId = SO.ObjectId
INNER JOIN OA ON A.ApplicantId = OA.ObjectId

WHERE SO.SectorId=47
AND A.StatusId IN (57,64,65,69)
AND OA.AttributeId IN (78,79,80,87,88,89,81,82,83,85,84,86)

Group by OA.AttributeId, A.StatusId
Order by Area

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-25 : 12:17:39

Instead of Counting in the case, do a SUM(Case When ..then 1 else 0 end)..the Group By would eliminate the duplicate areas and remove the zero rows.

SELECT 'Area' = CASE
WHEN OA.AttributeId=78 THEN 'North East England'
WHEN OA.AttributeId=79 THEN 'North West England'
WHEN OA.AttributeId=80 THEN 'Yorkshire & the Humber'
WHEN OA.AttributeId=81 THEN 'East Midlands'
WHEN OA.AttributeId=82 THEN 'West Midlands'
WHEN OA.AttributeId=83 THEN 'Eastern England'
WHEN OA.AttributeId=84 THEN 'South East England'
WHEN OA.AttributeId=85 THEN 'South West England'
WHEN OA.AttributeId=86 THEN 'London'
WHEN OA.AttributeId=87 THEN 'Scotland'
WHEN OA.AttributeId=88 THEN 'Wales'
WHEN OA.AttributeId=89 THEN 'Northern Ireland'
END
,'A1' =SUM( CASE
WHEN statusid='57' then 1 else 0
end)
,'B1' = SUM(CASE
WHEN statusid='64' then 1 else 0
end
,'C1' = SUM(CASE
WHEN statusid='65' then 1 else 0
end)
,'D1' = SUM(CASE
WHEN statusid='69' then 1 else 0
end)

FROM Applicants
INNER JOIN SO ON A.ApplicantId = SO.ObjectId
INNER JOIN OA ON A.ApplicantId = OA.ObjectId

WHERE SO.SectorId=47
AND A.StatusId IN (57,64,65,69)
AND OA.AttributeId IN (78,79,80,87,88,89,81,82,83,85,84,86)
GROUP BY CASE
WHEN OA.AttributeId=78 THEN 'North East England'
WHEN OA.AttributeId=79 THEN 'North West England'
WHEN OA.AttributeId=80 THEN 'Yorkshire & the Humber'
WHEN OA.AttributeId=81 THEN 'East Midlands'
WHEN OA.AttributeId=82 THEN 'West Midlands'
WHEN OA.AttributeId=83 THEN 'Eastern England'
WHEN OA.AttributeId=84 THEN 'South East England'
WHEN OA.AttributeId=85 THEN 'South West England'
WHEN OA.AttributeId=86 THEN 'London'
WHEN OA.AttributeId=87 THEN 'Scotland'
WHEN OA.AttributeId=88 THEN 'Wales'
WHEN OA.AttributeId=89 THEN 'Northern Ireland'
END

You could also move your AREA definition into a CTE

;WITH Areas
(SELECT ApplicationID,'Area' = CASE
WHEN OA.AttributeId=78 THEN 'North East England'
WHEN OA.AttributeId=79 THEN 'North West England'
WHEN OA.AttributeId=80 THEN 'Yorkshire & the Humber'
WHEN OA.AttributeId=81 THEN 'East Midlands'
WHEN OA.AttributeId=82 THEN 'West Midlands'
WHEN OA.AttributeId=83 THEN 'Eastern England'
WHEN OA.AttributeId=84 THEN 'South East England'
WHEN OA.AttributeId=85 THEN 'South West England'
WHEN OA.AttributeId=86 THEN 'London'
WHEN OA.AttributeId=87 THEN 'Scotland'
WHEN OA.AttributeId=88 THEN 'Wales'
WHEN OA.AttributeId=89 THEN 'Northern Ireland'
END)

Select A.Area
,'A1' =SUM( CASE
WHEN statusid='57' then 1 else 0
end)
,'B1' = SUM(CASE
WHEN statusid='64' then 1 else 0
end
,'C1' = SUM(CASE
WHEN statusid='65' then 1 else 0
end)
,'D1' = SUM(CASE
WHEN statusid='69' then count(statusid) else 0
end)

FROM Areas A
INNER JOIN SO ON A.ApplicantId = SO.ObjectId
INNER JOIN OA ON A.ApplicantId = OA.ObjectId

WHERE SO.SectorId=47
AND A.StatusId IN (57,64,65,69)
AND OA.AttributeId IN (78,79,80,87,88,89,81,82,83,85,84,86)
Group By Area

UNTESTED, but I think that should get you moving.

Poor planning on your part does not constitute an emergency on my part.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 12:19:09

Get rid of the statusid from the group by clause will remove the duplicates and probably the zeroes.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-25 : 12:40:21

quote:
Originally posted by nigelrivett

Get rid of the statusid from the group by clause will remove the duplicates and probably the zeroes.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sure you could do it the easy way too! :)

Poor planning on your part does not constitute an emergency on my part.

iradev
Starting Member

45 Posts

Posted - 2010-11-26 : 06:24:08

quote:
Originally posted by nigelrivett

Get rid of the statusid from the group by clause will remove the duplicates and probably the zeroes.

I tried that but I got an error:

Msg 8120, Level 16, State 1, Line 1
Column 'A.StatusId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY

Any ideas?

iradev
Starting Member

45 Posts

Posted - 2010-11-26 : 06:25:32

dataguru1971, your solution works perfectly, thank you!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 06:26:55

change things like
'B1' = CASE WHEN statusid='64' then count(statusid) else 0 end
to
B1 = sum(CASE WHEN statusid='64' then 1 else 0 end)

Don't use quotes for identifiers - if necessary us [B1].

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources