Student combinations

with cur_stu as (
  select sgbstdn_pidm, sgbstdn_levl_code, sgbstdn_styp_code, sgbstdn_coll_code_1, 
         sgbstdn_program_1, sgbstdn_majr_code_1
  from sgbstdn a
  where sgbstdn_stst_code = 'AS'
  and sgbstdn_term_code_eff = (select max(b.sgbstdn_term_code_eff)
                               from sgbstdn b
                               where a.sgbstdn_pidm = b.sgbstdn_pidm)
  )
  
select sgbstdn_levl_code, sgbstdn_program_1, sgbstdn_styp_code, sgbstdn_majr_code_1, count(*)
from cur_stu
group by sgbstdn_levl_code, sgbstdn_program_1, sgbstdn_styp_code, sgbstdn_majr_code_1
order by sgbstdn_levl_code, sgbstdn_program_1, sgbstdn_styp_code, sgbstdn_majr_code_1;