InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:38:33 GMT20http://www.sqlservercentral.com/Forums/Topic1393148-1292-1.aspx[quote][b]sanket kokane (12/10/2012)[/b][hr]please don't stop posting your questions..Just follow some rules ..People are happy to help you here Don't take anything Personally :-)[/quote]Especially such default rude and non-constructive answers from J.CELKO.I think he has a template somewhere...Saying that, you will definitely benefit from posting your question in line with forum etiquette as described in an article - link at the bottom of my signature;-)Tue, 11 Dec 2012 06:09:07 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1393148-1292-1.aspxplease don't stop posting your questions..Just follow some rules ..People are happy to help you here Don't take anything Personally :-)Mon, 10 Dec 2012 22:45:22 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1393148-1292-1.aspxThis 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!Sat, 08 Dec 2012 18:01:26 GMTandy 56206http://www.sqlservercentral.com/Forums/Topic1393148-1292-1.aspxPlease 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. <<I have no idea what “party and “child_party” mean, since you did not bother with DDL, but here is a way to get a complete report on these data elements. You will probably have to read about CUBE(), ROLLUP(), etc. SELECT CC.residency_zip, E.ethnicity_desc, COUNT(E.ethnicity_desc) AS ethnicity_cnt, COUNT(CC.child_party_id) AS party_cnt FROM Case_Child AS CC, Parties AS P, Ethnicity_Codes AS E WHERE CC.child_party_id = p.party_id AND p.ethnicity_code = E.ethnicity_code AND CC.program_id = 6 GROUP BY CUBE (CC.residency_zip, E.ethnicity_desc);Sat, 08 Dec 2012 17:48:42 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1393148-1292-1.aspx[quote][b]andy 56206 (12/5/2012)[/b][hr]..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)[/quote]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:[code="sql"]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 [/code][url]http://msdn.microsoft.com/en-us/library/ms189461(v=sql.100).aspx[/url]Wed, 05 Dec 2012 12:15:10 GMTChris Harshmanhttp://www.sqlservercentral.com/Forums/Topic1393148-1292-1.aspxyou 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.[code]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 [/code]Wed, 05 Dec 2012 12:05:27 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1393148-1292-1.aspxHi 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.ThanksAndyWed, 05 Dec 2012 11:32:38 GMTandy 56206