Try to optimized a PL/SQL query

Question

I am trying to improve the speed of the query below, but the required field cannot be changed. So I got stuck here. Please, help me out of this trap. A little hint or inspiration is also helpful!!

select cg.province_id,
   (select count(distinct(c.guidance_user_id))
      from case_guidance c
     where c.guidance_status = '2'
       and c.province_id = cg.province_id) as guidance_cnt,
   (select count(distinct(c.guidance_user_id))
      from case_guidance c
     where c.guidance_status = '2'
       and c.guidance_user_type = 'role.type.teacher'
       and c.province_id = cg.province_id) as guidance_teacher_cnt,
   (select count(distinct(c.guidance_user_id))
      from case_guidance c
     where c.guidance_status = '2'
       and c.guidance_user_type = 'role.type.jyy'
       and c.province_id = cg.province_id) as guidance_jyy_cnt,
   (select count(distinct(c.guidance_user_id))
      from case_guidance c
     where c.guidance_status = '2'
       and c.guidance_user_type = 'role.type.expert'
       and c.province_id = cg.province_id) as guidance_expert_cnt,
   (select count(distinct(c.case_id))
      from case_guidance c
     where c.guidance_status = '2'
       and c.province_id = cg.province_id) as guidance_case_cnt
from case_guidance cg
where cg.province_id is not null
group by cg.province_id
order by guidance_cnt desc

Answer 1

Replace the correlated subqueries with CASE to eliminate all joins:

select
    province_id,
    count(distinct(case when guidance_status = '2'                                              then guidance_user_id else null end)) guidance_cnt,
    count(distinct(case when guidance_status = '2' and guidance_user_type = 'role.type.teacher' then guidance_user_id else null end)) guidance_teacher_cnt,
    count(distinct(case when guidance_status = '2' and guidance_user_type = 'role.type.jyy'     then guidance_user_id else null end)) guidance_jyy_cnt,
    count(distinct(case when guidance_status = '2' and guidance_user_type = 'role.type.expert'  then guidance_user_id else null end)) guidance_expert_cnt,
    count(distinct(case when guidance_status = '2'                                              then case_id          else null end)) guidance_case_cnt
from case_guidance
group by province_id;
order by guidance_cnt desc

(I intentionally left the code lines extra long so that the conditions will line up. This helps make it clear what the difference between the columns are, and that they're all doing almost the exact same thing.)