How to return only one row per course

I am trying to return a list of course IDs that contain three components: assignments, discussions, and a syllabus. I want my results to have only one row per course ID with either Y’s or N’s indicating whether those courses have assignments, discussions, and a syllabus. However, I am getting every unique combination of Y’s and N’s for a given course ID.

How do I return only one row per course ID?

select distinct cm.course_id as Course, 
case
    when exists (select 1 where gt.name ilike 'Assignment%')
        then 'Y'
        else 'N' 
end as are_there_assignments,
case
when exists (select 1 where gt.name ilike 'Discussion%')
    then 'Y'
      else 'N' 
end as are_there_discussions,
case
when exists (select 1 where ct.label ilike '%syllabus%' )
    then 'Y'
      else 'N' 
end as is_there_a_syllabus
from course_main cm
left join course_course cc
    on cm.pk1 = cc.crsmain_pk1
inner join gradebook_main gm
    on cm.pk1 = gm.crsmain_pk1
inner join gradebook_type gt
    on gm.gradebook_type_pk1 = gt.pk1
inner join course_toc ct
    on cm.pk1 = ct.crsmain_pk1
where cc.crsmain_parent_pk1 is NULL 
and (cm.course_id ilike '%SPF-2020-C%'
                    or cm.course_id ilike '%SPF-2020-S%'
                    or cm.course_id ilike '%SP2-2020-C%'
                    or cm.course_id ilike '%SP2-2020-S%'
                    or cm.course_id ilike '%SP2-2020-A%'
                    or cm.course_id ilike '%SPF-2020-A%')
order by cm.course_id;

Here are my current results:

Answer

I think that you can do conditional aggregation. Starting from your existing query, that would look like:

select
    cm.course_id,
    max(case when gt.name ilike 'Assignment%' then 'Y' else 'N' end) are_there_assignments,
    max(case when gt.name ilike 'Discussion%' then 'Y' else 'N' end) are_there_discussions,
    max(case when ct.label ilike '%syllabus%' then 'Y' else 'N' end) is_there_a_syllabus
from ...
where ...
group by cm.course_id
order by cm.course_id

Advertisement

Answer