SQl query results by year

Question

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 percentage 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 an invoice 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 iterations of queries using an approximate account, but I can’t figure out how to get an account or a percentage of gender, homeless and unemployed. As soon as I did it, I think I can rotate the table to get the necessary screen.

Answer 1

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 do not know if you can have values ​​other than Men / Women, or Yes / No, for example, "unknown." In this case, you will need to decide whether, for example, 10 men, 5 women and 5 unknowns - 50% of men, that is, 50%, as you know, men or 66% of men, that is 66% of those whose gender is known are men.