SQl Query Results by Year

case, count, pivot, sql

Solution

I think this should do it:

select year(admit_date) as year_of_admit,
  sum(case when gender='Male' then 1 else 0 end)*100/count(*) as Male, 
  sum(case when gender='Female' then 1 else 0 end)*100/count(*) as Female, 
  sum(case when homeless='Yes' then 1 else 0 end)*100/count(*) as Homeless
from client
group by year(admit_date)

I don't know if you can have values other than Male/Female or Yes/No, like "unknown". In that case you'd have to decide whether, say, 10 males, 5 females, and 5 unknowns means 50% male, i.e. 50% are known to be male, or 66% males, i.e. 66% of those whose gender is known are male.

Problem

I have a Client table with the following columns. ``` Admit_date Gender Homeless Unemployed Deleted 4/2/2012 Male Yes Yes 0 1/1/2011 Female Yes Yes 0 12/2/2011 Male No No 0 5/23/2009 Female Yes Yes 0 4/3/2009 Male No No 0 7/4/2010 Male Yes Yes 0 9/2/2010 Male Yes Yes 0 ``` I need to show the percent of each group in each year. I think this will require a pivot table: ``` 2009 2010 2011 2012 Admitted 2 2 2 1 Male 50% 100% 50% 100% Female 50% 0 50% 0% Homeless 50% 100% 50% 100% Unemployed 50% 100% 50% 100% ``` This query gives me the count for each year: ``` select year(admit_date_c) as Year_of_Admit,count((admit_date_c)) as Admitted from clients where deleted = '0' group by year(admit_date_c) Year_of_Admit Admitted 2009 2 2010 2 2011 2 2012 1 ``` I tried numerous query iterations using Case Count but can't figure out how to get a count or percentage of Gender, Homeless and Unemployement. Once I have that, I think I can pivot the table to get the display I need.

Original source