데이터베이스 질문과 답변
SELECT STAT.CAPITALCD,
CODE.NAME AS 지역,
STAT.STATIONCD,
CODEST.NAME AS 동네,
SUM(CASE WHEN SCODE in ('0001','0401') THEN CNT ELSE 0 END) "A",
SUM(CASE WHEN SCODE in ('0002','0402') THEN CNT ELSE 0 END) "B",
SUM(CASE WHEN SCODE in ('0005','0405') THEN CNT ELSE 0 END) "C",
SUM(CASE WHEN SCODE in ('0008','0408') THEN CNT ELSE 0 END) "D"
FROM (select * from STAT_SERVCNT_2014 where daytime between '20140101' and '20140132') STAT
LEFT JOIN (SELECT * FROM CODE WHERE INDEXED ='11')CODE
ON CODE.CODE=STAT.CAPITALCD
LEFT JOIN NPA_CODEST CODEST
ON CODEST.INDEXED = STAT.CAPITALCD
AND CODEST.CODE=STAT.STATIONCD
GROUP BY STAT.CAPITALCD,CODE.NAME, STAT.STATIONCD,CODEST.NAME
ORDER BY STAT.CAPITALCD, STAT.STATIONCD;
위 쿼리가 있습니다.
해당 쿼리로 날짜로 지정해서 지정한 날짜에 대해서만 쿼리를 뽑고 있는데..
FROM (select * from STAT_SERVCNT_2014 where daytime between '20140101' and '20140132') STAT
이걸 where 조건 없이 월별 통계로 뽑고 싶은데...
어떻게 해야 할지 모르겠습니다.
도움 부탁드립니다.
SELECT substr(STAT.daytime,1,6) yyyymm
STAT.CAPITALCD,
CODE.NAME AS 지역,
STAT.STATIONCD,
CODEST.NAME AS 동네,
SUM(CASE WHEN SCODE in ('0001','0401') THEN CNT ELSE 0 END) "A",
SUM(CASE WHEN SCODE in ('0002','0402') THEN CNT ELSE 0 END) "B",
SUM(CASE WHEN SCODE in ('0005','0405') THEN CNT ELSE 0 END) "C",
SUM(CASE WHEN SCODE in ('0008','0408') THEN CNT ELSE 0 END) "D"
FROM STAT_SERVCNT_2014 STAT
LEFT JOIN (SELECT * FROM CODE WHERE INDEXED ='11')CODE
ON CODE.CODE=STAT.CAPITALCD
LEFT JOIN NPA_CODEST CODEST
ON CODEST.INDEXED = STAT.CAPITALCD
AND CODEST.CODE=STAT.STATIONCD
GROUP BY substr(STAT.daytime,1,6), STAT.CAPITALCD,CODE.NAME, STAT.STATIONCD,CODEST.NAME
ORDER BY substr(STAT.daytime,1,6), STAT.CAPITALCD, STAT.STATIONCD;
아,, group by 도 저렇게 사용 가능하군요...
너무 감사합니다!
) 버튼을 클릭하여 작성 하시면 됩니다.