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;
|