Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help getting totals by ZipCode Expand / Collapse
Author
Message
Posted Wednesday, December 5, 2012 11:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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), 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
Post #1393148
Posted Wednesday, December 5, 2012 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 12,905, Visits: 32,161
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

--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 5, 2012 12:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 1,907, Visits: 2,056
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
Post #1393163
Posted Saturday, December 8, 2012 5:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 1,945, Visits: 3,064
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. <<

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


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1394365
Posted Saturday, December 8, 2012 6:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
please don't stop posting your questions..
Just follow some rules ..People are happy to help you here
Don't take anything Personally


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1394892
Posted Tuesday, December 11, 2012 6:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 2,873, Visits: 5,185
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1395078
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse