Finding Percentage of Races - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding Percentage of Races

Author

Topic

sachin.hingole
Starting Member

15 Posts

Posted - 2008-12-23 : 02:13:19

Hi everybody
I have to find out the % of races from the table my table has the records as follows

I am joining organiztion table with its employee then with races
and finding count by grouping the races
its giving me below table

OrganizationName | Races | Count
Test1 |African American | 2
Test1 |American Indian | 4
Test1 |Asian | 3
Test2 |American | 6
Test2 |American Indian | 23
Test2 |Asian | 5

so I need % of "African American" and % of "American Indian" and % of "Asian" from org Test1 same for Test2 organization

and here Races are of type

6 as follows

1. White
2. African American
3. Hispanic
4. Asian
5. American Indian
6. Other

Thanks in advance

Sachin Hingole

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-23 : 04:30:31

SELECT t.OrganizationName,t.Races,r.percentage FROM
yourtable t
CROSS APPLY
(SELECT t.count/CONVERT(DECIMAL(18,2),SUM(count)) AS 'percentage' FROM yourtable WHERE OrganizationName = t.OrganizationName) r

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-23 : 05:26:16

SELECT e.test,t1.count/CONVERT(DECIMAL(18,2),SUM(e.count)) AS 'percentage' FROM ex e
inner join ex AS t1 on t1.test = e.test group by e.test,t1.count

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources