WITH tbl AS ( SELECT '2011' YYYY, '남' SexTyp FROM dual UNION ALL SELECT '2011' YYYY, '남' SexTyp FROM dual UNION ALL SELECT '2011' YYYY, '여' SexTyp FROM dual UNION ALL SELECT '2012' YYYY, '남' SexTyp FROM dual UNION ALL SELECT '2012' YYYY, '남' SexTyp FROM dual ), gender as (select '남' SexTyp from dual union all select '여' from dual) select r.yyyy, l.sextyp, nvl(cnt, 0) cnt from gender l left outer join (Select YYYY, SexTyp, Sum(1) Cnt From tbl Group By YYYY, SexTyp ) r partition by (r.yyyy) on (l.sextyp = r.sextyp)