CODEWITHSUNDEEP
sqlsql-serversql-server-2008
morne
morne

Reputation: 4189

SQL count unique occurrences of gender

I have 3 Tables (Demographic),(LINK) and (Admission)

SELECT SUM(CASE dmg.dmg_Sex WHEN 'Male' THEN 1 END) AS male,
       SUM(CASE dmg.dmg_Sex WHEN 'Female' THEN 1 END) AS female,
FROM Demographic dmg
JOIN LINK lnk ON lnk.lnk_dmgID = dmg.dmg_ID
JOIN Admission adm ON adm.adm_ID = lnk.lnk_admID
WHERE adm.adm_ReferralDate >= '01/01/2011' AND adm.adm_ReferralDate <= '31/03/2011'

Im trying to sum the genders in date range. This is not working though, Am I on the right track?

Just doing the plain query without the SUM and CASE works 100%.

enter image description here

Upvotes: 0

Views: 144

Answers (2)

Jose Tuttu
Jose Tuttu

Reputation: 428

SELECT SUM(CASE dmg.dmg_Sex WHEN 'Male' THEN 1 END) AS male,
       SUM(CASE dmg.dmg_Sex WHEN 'Female' THEN 1 END) AS female
FROM Demographic dmg
JOIN LINK lnk ON lnk.lnk_dmgID = dmg.dmg_ID
JOIN Admission adm ON adm.adm_ID = lnk.lnk_admID
WHERE adm.adm_ReferralDate >= '01/01/2011' AND adm.adm_ReferralDate <= '31/03/2011'

Upvotes: 2

Matt
Matt

Reputation: 15061

Use GROUP BY

SELECT dmg.dmg_Sex, COUNT (dmg.dmg_Sex)
FROM Demographic dmg
JOIN LINK lnk ON lnk.lnk_dmgID = dmg.dmg_ID
JOIN Admission adm ON adm.adm_ID = lnk.lnk_admID
WHERE adm.adm_ReferralDate >= '01/01/2011' 
AND adm.adm_ReferralDate <= '31/03/2011'
GROUP BY dmg.dmg_Sex

Upvotes: 1

Related Questions