select concat(left(sdate,4),'-',substring(sdate,6,2)) ym, count(distinct(kno)) Cnt from program where agroup = '0601' group by ym order by ym asc
안녕하세요 날짜관련 쿼리 질문드리겠습니다.
쿼리실행결과가 첨부한 파일입니다.
만약에 등록된 kno값이 없어도 해당월에 0으로 나오게 할수있을까요?
2018-01 72
2018-02 112
2018-03 124
2018-04 0
2018-05 0
.......
2018-10 1
이런식으로 조회를 하고싶습니다.
SELECT a.ym , IFNULL(cnt, 0) cnt FROM (-- 기준 월 집합 -- SELECT '201712' ym UNION ALL '201801' UNION ALL '201802' UNION ALL '201803' UNION ALL '201804' UNION ALL '201805' UNION ALL '201806' UNION ALL '201807' UNION ALL '201808' UNION ALL '201809' UNION ALL '201810' UNION ALL '201811' UNION ALL '201812' UNION ALL '201901' ) a INNER JOIN (SELECT DATE_FORMAT(MIN(sdate), '%Y%m') sym , DATE_FORMAT(MAX(sdate), '%Y%m') eym FROM program WHERE agroup = '0601' ) b ON a.ym BETWEEN b.sym AND b.eym LEFT OUTER JOIN (SELECT DATE_FORMAT(sdate, '%Y%m') ym , COUNT(DISTINCT kno) cnt FROM program WHERE agroup = '0601' GROUP BY DATE_FORMAT(sdate, '%Y%m') ) c ON a.ym = c.ym ORDER BY ym ;