WITH t AS ( SELECT '201810' yymm, 1 tot_cnt FROM dual UNION ALL SELECT '201811', 1 FROM dual UNION ALL SELECT '201810', 1 FROM dual UNION ALL SELECT '201811', 1 FROM dual UNION ALL SELECT '201810', 1 FROM dual UNION ALL SELECT '201810', 3 FROM dual UNION ALL SELECT '201811', 1 FROM dual UNION ALL SELECT '201811', 2 FROM dual UNION ALL SELECT '201811', 2 FROM dual UNION ALL SELECT '201811', 1 FROM dual UNION ALL SELECT '201810', 2 FROM dual UNION ALL SELECT '201811', 1 FROM dual UNION ALL SELECT '201810', 2 FROM dual UNION ALL SELECT '201810', 1 FROM dual UNION ALL SELECT '201811', 1 FROM dual UNION ALL SELECT '201810', 1 FROM dual UNION ALL SELECT '201811', 3 FROM dual ) -- Group By & Decode SELECT LEAST(tot_cnt, 3) tot_cnt , COUNT(DECODE(yymm, '201810', 1)) "201810" , COUNT(DECODE(yymm, '201811', 1)) "201811" FROM t WHERE yymm IN ('201810', '201811') GROUP BY LEAST(tot_cnt, 3) ; -- Pivot -- SELECT * FROM (SELECT yymm , LEAST(tot_cnt, 3) tot_cnt FROM t WHERE yymm IN ('201810', '201811') ) PIVOT (COUNT(*) FOR yymm IN ('201810' "201810", '201811' "201811")) ;