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 05, 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 05, 2012 12:05 PM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 8:03 PM Points: 12,247, Visits: 29,370
 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--There is no spoon, and there's no default ORDER BY in sql server either.Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1393157
 Posted Wednesday, December 05, 2012 12:15 PM
 SSCommitted Group: General Forum Members Last Login: 2 days ago @ 10:34 AM Points: 1,678, Visits: 1,840
 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 08, 2012 5:48 PM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 15, 2013 11:11 AM Points: 1,945, Visits: 2,782
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema arE. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible and not local dialect. This is minimal polite behavior on SQL forums. Now we have to guess, so we can do your typing and your job for you. We do not use the 1970's Sybase CONVERT () string function any more; we have the ANSI/ISO CAST().We do not do the percentage computation in the database. You have destroyed information given by the COUNT() functions. Let the host program do that tier in the C/S system. >> I am trying to get grand total, totals of each ethnicity and percentage of each ethnicity by zip_code. <
Post #1394365
 Posted Saturday, December 08, 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: Thursday, August 01, 2013 10:17 AM Points: 323, Visits: 984