help with count - 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)

help with count

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-11-10 : 06:44:07

Hello everyone,
Basically I have the following code below which outputs applicant details including the geographical area they have registered from. If you look in the where clause, all the geographical areas are split in 4 teams. What I need to do is add another 4 cloumns: TeamA, TeamB, TeamC and TeamD and then count the number of applicants for each team. I could get rid of all other columns I currently have, if that would make it easier. All I need is to produce a report that outputs the number of applicants for each team. I have no idea how to approach this so any help would be appreciated! Thanks

 

SELECT DISTINCT
Apps.ApplicantId
,Objects.FullName
,Apps.CreatedDate
,'Area' = CASE 
WHEN dbo.ObAtts.AttributeId=78 THEN 'North East'
WHEN dbo.ObAtts.AttributeId=79 THEN 'North West'
WHEN dbo.ObAtts.AttributeId=80 THEN 'Yorkshire'
WHEN dbo.ObAtts.AttributeId=81 THEN 'East Midlands'
WHEN dbo.ObAtts.AttributeId=82 THEN 'West Midlands'
WHEN dbo.ObAtts.AttributeId=83 THEN 'Eastern England'
WHEN dbo.ObAtts.AttributeId=84 THEN 'South East England'
WHEN dbo.ObAtts.AttributeId=85 THEN 'South West England'
WHEN dbo.ObAtts.AttributeId=86 THEN 'London'
WHEN dbo.ObAtts.AttributeId=87 THEN 'Scotland'
WHEN dbo.ObAtts.AttributeId=88 THEN 'Wales'
WHEN dbo.ObAtts.AttributeId=89 THEN 'Northern Ireland'
END

FROM Apps
INNER JOIN Objects ON Apps.ApplicantId = Objects.Objectid
LEFT JOIN Sector ON Applicants.ApplicantId = Sector.ObjectId
INNER JOIN ObAtts ON Apps.ApplicantId = ObAtts.ObjectId

WHERE Sector.SectorId=9
AND (Apps.StatusId) IN (57,58,60,61,62,63,64,65,69) 
AND Apps.CreatedDate BETWEEN '09/01/2010' AND '10/01/2010'
AND ( --Team A	
       ((ObAtts.AttributeId IN (78,79,80,87,88,89)))
      --Team B
      OR ((ObAtts.AttributeId IN (81,82,83,85)))
      --Team C
      OR ((ObAtts.AttributeId=84))
      --Team D
      OR ((ObAtts.AttributeId=86)))

ORDER BY Area

Humate
Posting Yak Master

101 Posts

Posted - 2010-11-10 : 07:00:12

Not 100% sure without the tables, but something like:

SELECT

CASE WHEN
ObAtts.AttributeId IN (78,79,80,87,88,89) THEN TeamA
WHEN ObAtts.AttributeId IN (81,82,83,85) THEN TeamB
WHEN ObAtts.AttributeId =84 THEN TeamC
WHEN ObAtts.AttributeId =86 THEN TeamD END as Team,

COUNT(*) as Applicants

FROM Apps
INNER JOIN Objects ON Apps.ApplicantId = Objects.Objectid
LEFT JOIN Sector ON Applicants.ApplicantId = Sector.ObjectId
INNER JOIN ObAtts ON Apps.ApplicantId = ObAtts.ObjectId

WHERE Sector.SectorId=9
AND (Apps.StatusId) IN (57,58,60,61,62,63,64,65,69)
AND Apps.CreatedDate BETWEEN '09/01/2010' AND '10/01/2010'

iradev
Starting Member

45 Posts

Posted - 2010-11-10 : 07:49:43

quote:
Originally posted by Humate

Not 100% sure without the tables, but something like:

SELECT

CASE WHEN
ObAtts.AttributeId IN (78,79,80,87,88,89) THEN TeamA
WHEN ObAtts.AttributeId IN (81,82,83,85) THEN TeamB
WHEN ObAtts.AttributeId =84 THEN TeamC
WHEN ObAtts.AttributeId =86 THEN TeamD END as Team,

COUNT(*) as Applicants

FROM Apps
INNER JOIN Objects ON Apps.ApplicantId = Objects.Objectid
LEFT JOIN Sector ON Applicants.ApplicantId = Sector.ObjectId
INNER JOIN ObAtts ON Apps.ApplicantId = ObAtts.ObjectId

WHERE Sector.SectorId=9
AND (Apps.StatusId) IN (57,58,60,61,62,63,64,65,69)
AND Apps.CreatedDate BETWEEN '09/01/2010' AND '10/01/2010'

Thanks Humate! I got the following output:


Team   Applicant
-----------------
TeamA	1
TeamA	8
TeamA	8
TeamB	5
TeamB	8
TeamB	13
TeamC	28
TeamB	9
TeamD	57
TeamA	1
TeamA	4

Any idea how I can get the total for each team, like
Team A 30
Team B 35

instead of the many team rows?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-10 : 08:04:11

select Team,sum(Applicants) as Applicants from
(
SELECT
CASE WHEN
ObAtts.AttributeId IN (78,79,80,87,88,89) THEN TeamA
WHEN ObAtts.AttributeId IN (81,82,83,85) THEN TeamB
WHEN ObAtts.AttributeId =84 THEN TeamC
WHEN ObAtts.AttributeId =86 THEN TeamD END as Team,
COUNT(*) as Applicants
FROM Apps
INNER JOIN Objects ON Apps.ApplicantId = Objects.Objectid
LEFT JOIN Sector ON Applicants.ApplicantId = Sector.ObjectId
INNER JOIN ObAtts ON Apps.ApplicantId = ObAtts.ObjectId

WHERE Sector.SectorId=9
AND (Apps.StatusId) IN (57,58,60,61,62,63,64,65,69)
AND Apps.CreatedDate BETWEEN '09/01/2010' AND '10/01/2010'
) as t

Madhivanan

Failing to plan is Planning to fail

iradev
Starting Member

45 Posts

Posted - 2010-11-10 : 08:18:52

quote:
Originally posted by madhivanan

select Team,sum(Applicants) as Applicants from
(
SELECT
CASE WHEN
ObAtts.AttributeId IN (78,79,80,87,88,89) THEN TeamA
WHEN ObAtts.AttributeId IN (81,82,83,85) THEN TeamB
WHEN ObAtts.AttributeId =84 THEN TeamC
WHEN ObAtts.AttributeId =86 THEN TeamD END as Team,
COUNT(*) as Applicants
FROM Apps
INNER JOIN Objects ON Apps.ApplicantId = Objects.Objectid
LEFT JOIN Sector ON Applicants.ApplicantId = Sector.ObjectId
INNER JOIN ObAtts ON Apps.ApplicantId = ObAtts.ObjectId

WHERE Sector.SectorId=9
AND (Apps.StatusId) IN (57,58,60,61,62,63,64,65,69)
AND Apps.CreatedDate BETWEEN '09/01/2010' AND '10/01/2010'
) as t

Madhivanan

Failing to plan is Planning to fail

Thanks for the reply Madhivanan.
I'm getting:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ObjectAttributes.AttributeId" could not be bound.

Humate
Posting Yak Master

101 Posts

Posted - 2010-11-10 : 08:57:44

iradev
Starting Member

45 Posts

Posted - 2010-11-10 : 09:37:39

quote:
Originally posted by Humate

select Team,sum(Applicants) as Applicants from
(
SELECT
CASE WHEN
ObAtts.AttributeId IN (78,79,80,87,88,89) THEN TeamA
WHEN ObAtts.AttributeId IN (81,82,83,85) THEN TeamB
WHEN ObAtts.AttributeId =84 THEN TeamC
WHEN ObAtts.AttributeId =86 THEN TeamD END as Team,
COUNT(*) as Applicants
FROM Apps
INNER JOIN Objects ON Apps.ApplicantId = Objects.Objectid
LEFT JOIN Sector ON Applicants.ApplicantId = Sector.ObjectId
INNER JOIN ObAtts ON Apps.ApplicantId = ObAtts.ObjectId

WHERE Sector.SectorId=9
AND (Apps.StatusId) IN (57,58,60,61,62,63,64,65,69)
AND Apps.CreatedDate BETWEEN '09/01/2010' AND '10/01/2010'
) as t

GROUP BY Team

Perfect! Thank you both for the quick response

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources