June 19, 2009 at 1:31 pm
I need some help creating a T-SQL query and was hoping some of you folks could help me out. The query is from the same table. I have these two queries that I need to combine into a single query. They are as follows:
SELECT DISTINCT BENCAT,
COUNT(*) NumMales
FROM [dbo].[masterenroll]
WHERE DMIS = '0005' AND GENDER = 'M'
GROUP BY BENCAT
SELECT DISTINCT BENCAT,
COUNT(*) NumFemales
FROM [dbo].[masterenroll]
WHERE DMIS = '0005' AND GENDER = 'F'
GROUP BY BENCAT
The results of the above queries are as follows respectively:
BENCATNumMales
ADA 242
ADAF 1
ADFMLY2712
ADN 6
RTA 563
RTAF 398
RTFMLY390
RTN 59
And
BENCATNumFemales
ADA 129
ADAF 2
ADFMLY5309
ADN 3
RTA 33
RTAF 31
RTFMLY1308
RTN 6
The goal is to combine these two queries into one with results that look like this
The results are as follows respectively:
BENCATNumMales NumFemales
ADA 242 129
ADAF 1 2
ADFMLY2712 5309
ADN 6 3
RTA 563 33
RTAF 398 31
RTFMLY390 1308
RTN 59 6
I appreciate your help!
Thanks in advance!
June 19, 2009 at 1:49 pm
SELECT BENCAT,
SUM(CASE Gender WHEN 'M' THEN 1 ELSE 0 END) AS NumMales,
SUM(CASE Gender WHEN 'F' THEN 1 ELSE 0 END) AS NumFemales
FROM [dbo].[masterenroll]
WHERE DMIS = '0005'
GROUP BY BENCAT
The distinct's unnecessary because there's a group by and aggregations in the outer query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2009 at 1:54 pm
Holy Crap Dude, that's awsome. Works perfectly!
I really appreciate it. 🙂
June 19, 2009 at 1:55 pm
Here's one way to do this. This is using a CTE (assuming you're using 2005 or higher). If not, it is easy enough to wrap up as a subquery.
WITH CAT AS
(
SELECT BENCAT,
CASE WHEN GENDER='M' THEN 1 ELSE 0 END AS IsMale,
CASE WHEN GENDER='F' THEN 1 ELSE 0 END AS IsFemale
FROM [dbo].[masterenroll]
WHERE DMIS = '0005'
)
SELECT BENCAT,
SUM(IsMale) AS Males,
SUM(IsFemale) AS Females
FROM CAT
GROUP BY BENCAT;
Let me know if that works.
Cheers,
Brian
June 19, 2009 at 2:04 pm
beezell (6/19/2009)
Here's one way to do this. This is using a CTE (assuming you're using 2005 or higher). If not, it is easy enough to wrap up as a subquery.WITH CAT AS
(
SELECT BENCAT,
CASE WHEN GENDER='M' THEN 1 ELSE 0 END AS IsMale,
CASE WHEN GENDER='F' THEN 1 ELSE 0 END AS IsFemale
FROM [dbo].[masterenroll]
WHERE DMIS = '0005'
)
SELECT BENCAT,
SUM(IsMale) AS Males,
SUM(IsFemale) AS Females
FROM CAT
GROUP BY BENCAT;
Let me know if that works.
Cheers,
Brian
This works good too! Thanks Again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply