Tally survey result problem - 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)

Tally survey result problem

Author

Topic

charzweb
Starting Member

2 Posts

Posted - 2007-06-15 : 12:31:31

I need to create a stored procedure that will tally EEO survey information.

Table1 contains Ethnicity types that need to be reported on (ex: RaceA, RaceB, etc.)

Table2 contains survey results with Race and Gender
Ex:
RaceA Male
RaceC Male
RaceA Female
RaceB Decline
RaceB Male
RaceA Female

I would like the results to be saved in a temporary table like this:

Ethnicity Male Female Decline Total
RaceA 3 5 1 9
RaceB 6 3 2 11

My thoughts are to spin through Table1. While at each Table1 row, count the distinct rows where Table1.Ethnicity = Table2.Race for each gender and total. I am having trouble creating the SQL for this procedure. Any code tips would be appreciated!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-15 : 12:53:37

[code]
Select
race as Ethnicity,
sum(case when gender='Male' then 1 else 0 end) as Male,
sum(case when gender='Female' then 1 else 0 end) as Female,
sum(case when gender='Decline' then 1 else 0 end) as Decline
from
table
group by
race[/code]
Also read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

charzweb
Starting Member

2 Posts

Posted - 2007-06-15 : 14:40:56

That is extremely close to what I need! How do I include all races from Table1 even if no rows in Table2 associated with that race. Ex: Table1 contains a row with Ethnicity = RaceF, but Table2 does not include any rows that have Race = RaceF. I still need to have this row shown in the final results. Output: RaceF 0 0 0 0

Thank you so much for your expertise!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-15 : 21:53:57

quote:
Originally posted by charzweb

That is extremely close to what I need! How do I include all races from Table1 even if no rows in Table2 associated with that race. Ex: Table1 contains a row with Ethnicity = RaceF, but Table2 does not include any rows that have Race = RaceF. I still need to have this row shown in the final results. Output: RaceF 0 0 0 0

Thank you so much for your expertise!!!

use LEFT JOIN from table1 to table2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources