How to calculate percentage in sql which result will appear in different columns?

Question

I have to calculate percentage based on different ranking class which is 1st or 2nd. Below is a sample data table.

Year   Grading_PerID  Candidate_ID   Ranking
-----  ------------   ------------   -------
2007        1              1            1  
2007        1              1            2
2007        2              2            1
2007        3              2            1
2008        4              1            2
2008        3              2            1
2008        2              2            2 
2008        1              1            2
2009        2              1            1   
2009        3              1            2
2009        4              2            1
2009        1              2            1  

I want to calculate from 2007 to 2009, how many times candidate_1 and candidate_2 are graded in each year and the proportions are respectively that candidate_1 and candidate_2 are graded as 1st and 2nd. The expecting output sample is given below:

Expecting Out Put :

Candidate_ID   Year   Count    1_rank   2_rank
------------   ----   -----    ------   ------
     1         2007     2         %        %
     1         2008     2         %        %
     1         2009     2         %        %
     2         2007     2         %        %
     2         2008     2         %        %
     2         2009     2         %        %

I have done the part without rank percentages by using the sql below:

select candidate_id , year, count(*) as count                    
from myTable                                   
group by candidate_id, year;

But I have no idea on how to calculate the ranking percentage for each year. How could I do to achieve that?

Answer 1

You can use SUM(CASE WHEN ...) to conditionally count the number of 1 and 2 ranks for each group:

select candidate_id, year, count(*) as count,
    100*SUM(case when Ranking = 1 then 1 else 0 end) AS 1_rank,
    100*SUM(case when Ranking = 2 then 1 else 0 end) AS 2_rank,
from myTable                                   
group by candidate_id, year;

Answer 2

as per my understanding ranking percentage is how many time in a year a candidate is allocated with rank 1 or 2. for eg. in 2008 candidate 1 is assigned to rank 2 two times. SO rank 2 percentage will be 100% in his case and rank 1 percentage will be 0. Below is my query, let me know if you have any doubt

create table mytable (yr number,grade_id number,c_id number,rank number);

select a.c_id,a.yr,cnt1,NVL((b.cnt2/a.cnt1) * 100,0) rnk_1_percentage,NVL((c.cnt2/a.cnt1) * 100,0) rnk_2_percentage
from 
(select yr,c_id,count(*) cnt1 from mytable group by yr,c_id ) a,
(select yr,c_id,rank,count(*) cnt2 from mytable where rank=1 group by yr,c_id,rank ) b,
(select yr,c_id,rank,count(*) cnt2 from mytable where rank=2 group by yr,c_id,rank) c
where a.yr=b.yr(+)
and a.c_id=b.c_id(+)
and a.yr=c.yr(+)
and a.c_id=c.c_id(+)
order by a.yr;

OR

In oracle it is like below in support to @Tim Biegeleisen

select yr,c_id
,100*(SUM(decode(rank,1,1,0)) / count(*))  rank1
,100*(SUM(decode(rank,2,1,0)) / count(*)) rank2
 from mytable group by yr,c_id