Counts for different criteria in one row per field

Question

I have the following SQL

    select c.course_code, c.course_description, ri.defin, rm.defmaybe, ro.defout
from applicant a
    inner join  preference p on a.ref_num = p.ref_num and p.institution_code = 'UT'
    inner join  course c on p.course_code = c.course_code
    left join   (
                    select ref_num, course_code, offering_id, count(*) as defin
                    from preference_status
                    where component_group = 'RS'
                    and component_value = 'IN'
                    group by 1,2,3
                )   as ri on a.ref_num = ri.ref_num and p.course_code = ri.course_code and p.offering_id = ri.course_code
    left join   (
                    select ref_num, course_code, offering_id, count(*) as defmaybe
                    from preference_status
                    where component_group = 'RS'
                    and component_value = 'MAYBE'
                    group by 1,2,3
                )   as rm on a.ref_num = rm.ref_num and p.course_code = rm.course_code and p.offering_id = rm.course_code
    left join   (
                    select ref_num, course_code, offering_id, count(*) as defout
                    from preference_status
                    where component_group = 'RS'
                    and component_value = 'OUT'
                    group by 1,2,3
                )   as ro on a.ref_num = ro.ref_num and p.course_code = ro.course_code and p.offering_id = ro.course_code
group by 1,2,3,4,5
order by 1
;

However, I am getting zero counts per left join in the select. I've tried different variations including adding the subquery select count(*) from in the select with no luck either.

Any ideas would be greatly appreciated.

Answer 1

Although this probably won't fix your problem, there is no need for multiple subqueries:

select c.course_code, c.course_description, r.defin, r.defmaybe, r.defout
from applicant a inner join
     preference p
     on a.ref_num = p.ref_num and p.institution_code = 'UT' inner join
     course c
     on p.course_code = c.course_code left join
     (select ref_num, course_code, offering_id,
             sum(case when component_value = 'IN' then 1 else 0 end) as defin,
             sum(case when component_value = 'MAYBE' then 1 else 0 end) as defmaybe,
             sum(case when component_value = 'OUT' then 1 else 0 end) as defout
      from preference_status
      where component_group = 'RS'
      group by 1, 2, 3
     ) r
     on a.ref_num = r.ref_num and p.course_code = r.course_code and p.offering_id = r.course_code
group by 1, 2, 3, 4, 5
order by 1;

I don't know if this will fix your problem, though.

Answer 2

Solution:

select c.course_code, c.course_description, sum(case when component_value = 'IN' then 1 else 0 end) as defin,
             sum(case when component_value = 'MAYBE' then 1 else 0 end) as defmaybe,
             sum(case when component_value = 'OUT' then 1 else 0 end) as defout
from applicant a 
    inner join preference p on a.ref_num = p.ref_num and p.institution_code = 'UT' 
    inner join course c on p.course_code = c.course_code
    inner join preference_status s  on a.ref_num = s.ref_num and p.course_code = s.course_code and p.offering_id = s.offering_id 
                                    and s.component_group = 'RS'
group by 1, 2
order by 1
;