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;