SELECT newYnCnt FROM (SELECT newYnCnt FROM (SELECT SUM(CASE WHEN PM.NEWYN = 'Y' THEN PM.KNO ELSE 0 END) newYnCnt FROM (SELECT ACODE, ANAME, APRCODE FROM code WHERE USEYN = 'Y' AND CLCODE = 'AS' AND ALVL != '3') CO LEFT OUTER JOIN program_manage PM ON CO.ACODE = PM.AGROUP GROUP BY CO.ACODE ORDER BY CO.ACODE ASC) A GROUP BY AGROUP ORDER BY AGROUP) B
위 쿼리문에서 KNO를 중복제거를 하고싶어서 distinct를 쓰면 문법에러나 나는데
어떤식으로 수정을 하면될지 답변좀 부탁드리겠습니다.
SUM(CASE WHEN PM.NEWYN = 'Y' THEN DISTINCT(PM.KNO) ELSE 0 END)
1. 단순하게 오류 수정 및 원하는 결과를 얻으려면?
- 수정전 : SUM(CASE WHEN pm.newyn = 'Y' THEN pm.kno ELSE 0 END) newYnCnt
- 수정후 : COUNT(DISTINCT CASE WHEN pm.newyn = 'Y' THEN pm.kno END) newYnCnt
2. 전반적으로 쿼리 비효율을 제거해 보면.
SELECT co.acode , co.aname , COUNT(DISTINCT pm.kno) newYnCnt FROM code co LEFT OUTER JOIN program_manage pm ON co.acode = pm.agroup AND pm.newyn = 'Y' WHERE co.useyn = 'Y' AND co.clcode = 'AS' AND co.alvl != '3' GROUP BY co.acode, co.aname ORDER BY co.acode ;