Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Need help getting totals by ZipCode Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, December 5, 2012 11:32 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 11, 2012 7:50 AM Points: 9, Visits: 35
 Hi all,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), ResidencyZipFROM CaseChildWhere programID = xBut 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.ethnicityDescORDER 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)I am new at this so any help is greatly appreciated.ThanksAndy
Post #1393148
 Posted Wednesday, December 5, 2012 12:05 PM
 SSChampion Group: General Forum Members Last Login: Today @ 2:39 PM Points: 14,540, Visits: 38,379
 you can use a technique using SUM(CASE... to get subtotasl;you'll have to construct the multiple case statements to match your descriptions, i think.here's a prototype based on the query you posted.`SELECT cc.ResidencyZip AS ZIP, COUNT(e.EthnicityDesc) AS 'Record Count', SUM(CASE WHEN e.EthnicityDesc = 'White' THEN 1 ELSE 0 END) AS BENCOUNT1, SUM(CASE WHEN e.EthnicityDesc = 'Black' THEN 1 ELSE 0 END) AS BENCOUNT2, SUM(CASE WHEN e.EthnicityDesc = 'Asian' THEN 1 ELSE 0 END) AS BENCOUNT3, CONVERT(DECIMAL(18,2),1.0 * COUNT(e.EthnicityDesc)/COUNT(cc.ChildPartyID) * 100) AS 'Percentage' FROM CaseChild cc INNER JOIN Party p ON cc.childPartyID = p.partyID INNER JOIN Ethnicity e ON p.EthnicityID = e.EthnicityIDWHERE (cc.programID = 6)GROUP BY cc.ResidencyZipORDER BY cc.ResidencyZip ` Lowell--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1393157
 Posted Wednesday, December 5, 2012 12:15 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 2:43 PM Points: 2,516, Visits: 2,900
 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.ethnicityDescORDER 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
Post #1393163
 Posted Saturday, December 8, 2012 6:01 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 11, 2012 7:50 AM Points: 9, Visits: 35
 This is why I hesitate to post to this forum. I always seem to run into people like you.It was a simple question and if you do not have an answer why post!
Post #1394367
 Posted Monday, December 10, 2012 10:45 PM
 Old Hand Group: General Forum Members Last Login: Wednesday, May 11, 2016 11:41 PM Points: 323, Visits: 1,019