데이터베이스 질문과 답변
등급별 카운트와 백분율을 구해야되는데요
테이블 기준으로 보았을떄 B에대한 값이 없는경우 카운트를 0으로 채워야 하구요
B가 아니더라도 A~F까지 값이 없는경우는 0으로 채워야 됩니다. 어떻게 채워야 될까요?ㅠ
백분율 같은경우는 쉽게 구할수 있는 방법이 있을까요?
쿼리(postgresql) 고수님들의 도움 및 조언 부탁 드립니다~ㅠ
by 마농
[2020.07.02 01:45:24]
WITH code_t AS
(
SELECT 'A' cd
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
)
, data_t AS
(
SELECT 'A' cd
UNION ALL SELECT 'A'
UNION ALL SELECT 'A'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'D'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'E'
UNION ALL SELECT 'E'
UNION ALL SELECT 'E'
UNION ALL SELECT 'E'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
UNION ALL SELECT 'F'
UNION ALL SELECT 'F'
)
SELECT a.cd
, COUNT(b.cd) cnt
, ROUND(COUNT(b.cd) / SUM(COUNT(b.cd)) OVER() * 100, 2) rat
FROM code_t a
LEFT OUTER JOIN data_t b
ON a.cd = b.cd
GROUP BY a.cd
;
by 춘
[2020.07.02 02:48:32]
WITH t AS
(
SELECT 'A' data FROM dual
UNION ALL
SELECT 'A' FROM dual
UNION ALL
SELECT 'A' FROM dual
UNION ALL
SELECT 'C' FROM dual
UNION ALL
SELECT 'D' FROM dual
UNION ALL
SELECT 'D' FROM dual
UNION ALL
SELECT 'D' FROM dual
UNION ALL
SELECT 'E' FROM dual
UNION ALL
SELECT 'E' FROM dual
UNION ALL
SELECT 'E' FROM dual
UNION ALL
SELECT 'E' FROM dual
UNION ALL
SELECT 'E' FROM dual
UNION ALL
SELECT 'E' FROM dual
UNION ALL
SELECT 'F' FROM dual
UNION ALL
SELECT 'F' FROM dual
UNION ALL
SELECT 'F' FROM dual
)
SELECT
b.str AS grade
, COUNT(a.data) AS cnt
, ROUND((RATIO_TO_REPORT( COUNT(a.data)) OVER() * 100), 2)||'%' report
FROM t a RIGHT JOIN (
SELECT
str
FROM t a, (SELECT SUBSTR('ABCDEFGHIZKLMNOPQRSTUVWXYZ', LEVEL, 1) AS str FROM dual CONNECT BY LEVEL <= 26)
WHERE str <= a.data
GROUP BY str
) b
ON a.data = b.str
GROUP BY b.str
ORDER BY b.str
) 버튼을 클릭하여 작성 하시면 됩니다.