Need help getting totals by ZipCode

Question

Post reply

Need help getting totals by ZipCode

andy 56206

SSC Journeyman

Points: 79
More actions
December 5, 2012 at 11:32 am

#271210

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), ResidencyZip
FROM CaseChild
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
Count(e.EthnicityDesc)
I am new at this so any help is greatly appreciated.
Thanks
Andy

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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.EthnicityID

WHERE (cc.programID = 6)

GROUP BY cc.ResidencyZip

ORDER 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!

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

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

andy 56206 SSC Journeyman Points: 79 More actions · Answer 3

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!

sanket kokane Hall of Fame Points: 3465 More actions · Answer 4

please don't stop posting your questions..

Just follow some rules ..People are happy to help you here

Don't take anything Personally 🙂

-----------------------------------------------------------------------------
संकेत कोकणे

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

sanket kokane (12/10/2012)
please don't stop posting your questions..
Just follow some rules ..People are happy to help you here
Don't take anything Personally 🙂

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;-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]