andy 56206 (12/5/2012)
..I am using the following code:SELECT cc.ResidencyZip as ZIP, e.EthnicityDesc,
Count(e.EthnicityDesc) as 'Record Count',
Convert(decimal(18,2),1.0 * COUNT(e.EthnicityDesc)/COUNT(cc.ChildPartyID) * 100) AS 'Percentage'
FROM CaseChild cc JOIN
Party p on cc.childPartyID = p.partyID JOIN
Ethnicity e on p.EthnicityID = e.EthnicityID
WHERE (cc.programID = 6)
GROUP BY cc.ResidencyZip,e.ethnicityDesc
ORDER BY cc.ResidencyZip
I ge the proper totals per ethnicitydesc but all the percentages are 100% and the Count(cc.ChildPartyID) is now Equal to
Count(e.EthnicityDesc)
the COUNT function simply counts all the rows per grouping based on your GROUP BY clause, not based on values of specific columns. If you want to calculate percents, you'd probably want to use COUNT with an OVER clause such as:
SELECT cc.ResidencyZip as ZIP, e.EthnicityDesc,
Count(*) as record_count,
Convert(decimal(18,2),100.0 * COUNT(*) / COUNT(*) OVER (PARTITION BY ResidencyZip)) AS ethnicity_percent
FROM CaseChild cc
JOIN Party p on cc.childPartyID = p.partyID
JOIN Ethnicity e on p.EthnicityID = e.EthnicityID
WHERE (cc.programID = 6)
GROUP BY cc.ResidencyZip, e.ethnicityDesc
ORDER BY cc.ResidencyZip
http://msdn.microsoft.com/en-us/library/ms189461(v=sql.100).aspx