년도별 통계 카운드
SELECT YYYY,
SUM(MON01) MON1,
SUM(MON02) MON2,
SUM(MON03) MON3,
SUM(MON04) MON4,
SUM(MON05) MON5,
SUM(MON06) MON6,
SUM(MON07) MON7,
SUM(MON08) MON8
FROM
(
SELECT SUBSTR(NM_DATE,1,4) as yyyy,
DECODE(SUBSTR(NM_DATE,6,2),'01',1,0) AS MON01,
DECODE(SUBSTR(NM_DATE,6,2),'02',1,0) AS MON02,
DECODE(SUBSTR(NM_DATE,6,2),'03',1,0) AS MON03,
DECODE(SUBSTR(NM_DATE,6,2),'04',1,0) AS MON04,
DECODE(SUBSTR(NM_DATE,6,2),'05',1,0) AS MON05,
DECODE(SUBSTR(NM_DATE,6,2),'06',1,0) AS MON06,
DECODE(SUBSTR(NM_DATE,6,2),'07',1,0) AS MON07,
DECODE(SUBSTR(NM_DATE,6,2),'08',1,0) AS MON08
FROM GONGJI
WHERE NM_DATE BETWEEN '2009/01/01' AND '2012/08/31'
) GROUP BY YYYY
ORDER BY yyyy
결과값ex)
yyyy mon01 mon02 mon03 mon04 mon05 mon06 mon07 mon08
2009 7 10 15 10 6 19 12 11
2010 1 0 0 0 0 0 0 2
2011 0 0 0 0 0 0 1 0