I am trying to get grand total, totals of each ethnicity and percentage of each ethnicity by zipcode.
I can get the total number by zipcode without adding the ethnicity portion to the query by using:
SELECT Count(ChildPartyID), ResidencyZip
Where programID = x
But I need to be able to display the toal number per zip AND further break down the data by ethnicity.
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
I am new at this so any help is greatly appreciated.