# 나름대로 짜 보았는데, 생각보다 깨끗하지가 않아서 문의 드려 봅니다.. 혹 제가 짠 것 보다 더 개선의 방법이 있을 지 문의 드립니다! SELECT LEFT(TA_D, 4) AS YM, SUM(AMT), SUM(CNT) FROM GN_SEXAGE WHERE TA_D >= '20190101' #CONCAT(START_YEAR, '0101') AND TA_D <= '20201231' #CONCAT(FINISH_YEAR, '1231') AND HD_CD like '4812961000%' #CONCAT(SGG,HD) GROUP BY LEFT(TA_D, 4) ORDER BY LEFT(TA_D, 4); #분기별 선택시 SELECT CASE WHEN RIGHT(TA_D,4) BETWEEN '0101' AND '0331' THEN CONCAT(LEFT(TA_D,4),'1Q') WHEN RIGHT(TA_D,4) BETWEEN '0401' AND '0631' THEN CONCAT(LEFT(TA_D,4),'2Q') WHEN RIGHT(TA_D,4) BETWEEN '0701' AND '0931' THEN CONCAT(LEFT(TA_D,4),'3Q') WHEN RIGHT(TA_D,4) BETWEEN '1001' AND '1231' THEN CONCAT(LEFT(TA_D,4),'4Q') ELSE 'NONE' END AS 'YYYYQQ' , SUM(AMT) AS AMT , SUM(CNT) AS CNT FROM GN_SEXAGE WHERE TA_D >= '20190101' #CONCAT(START_YEAR,'0101') AND TA_D <= '20201231' #CONCAT(FINISH_YEAR,'1231') AND HD_CD like '4812961000%' #CONCAT(SGG,HD) GROUP BY CASE WHEN RIGHT(TA_D,4) BETWEEN '0101' AND '0331' THEN CONCAT(LEFT(TA_D,4),'1Q') WHEN RIGHT(TA_D,4) BETWEEN '0401' AND '0631' THEN CONCAT(LEFT(TA_D,4),'2Q') WHEN RIGHT(TA_D,4) BETWEEN '0701' AND '0931' THEN CONCAT(LEFT(TA_D,4),'3Q') WHEN RIGHT(TA_D,4) BETWEEN '1001' AND '1231' THEN CONCAT(LEFT(TA_D,4),'4Q') ELSE 'NONE' END ORDER BY CASE WHEN RIGHT(TA_D,4) BETWEEN '0101' AND '0331' THEN CONCAT(LEFT(TA_D,4),'1Q') WHEN RIGHT(TA_D,4) BETWEEN '0401' AND '0631' THEN CONCAT(LEFT(TA_D,4),'2Q') WHEN RIGHT(TA_D,4) BETWEEN '0701' AND '0931' THEN CONCAT(LEFT(TA_D,4),'3Q') WHEN RIGHT(TA_D,4) BETWEEN '1001' AND '1231' THEN CONCAT(LEFT(TA_D,4),'4Q') ELSE 'NONE' END; #월별 선택시 SELECT LEFT(TA_D, 6) AS YM, SUM(AMT), SUM(CNT) FROM GN_SEXAGE WHERE TA_D >= '20190101' #CONCAT(START_YEAR,'0101') AND TA_D <= '20201231' #CONCAT(FINISH_YEAR,'1231') AND HD_CD like '4812961000%' #CONCAT(SGG,HD) GROUP BY LEFT(TA_D, 6) ORDER BY LEFT(TA_D, 6);
-- MySQL -- SELECT gb , SUM(amt) amt , SUM(cnt) cnt FROM (SELECT CASE 'q' -- #조회구분 WHEN 'y' THEN SUBSTR(ta_d, 1, 4) WHEN 'm' THEN SUBSTR(ta_d, 1, 6) WHEN 'q' THEN CONCAT( SUBSTR(ta_d, 1, 4) , CEIL(SUBSTR(ta_d, 5, 2) / 3) , 'Q' ) END gb , amt , cnt FROM gn_sexage WHERE ta_d >= '20190101' -- #CONCAT(START_YEAR , '0101') AND ta_d <= '20201231' -- #CONCAT(FINISH_YEAR, '1231') AND hd_cd LIKE '4812961000%' -- #CONCAT(SGG, HD) ) a GROUP BY gb ;