데이터베이스 질문과 답변
안녕하세요
SELECT A.*
FROM (SELECT B.VALUE2 AS "지역구분"
, SUM(A)+SUM(B)+SUM(C) AS "대상건수"
, SUM(A) AS "미설치건수"
, SUM(B) AS "설치건수"
, SUM(C) AS "고장건수"
, ROUND((SUM(C) / SUM(B) * 100),2) AS "고장율"
, ROUND((SUM(B) / (SUM(A)+SUM(B)+SUM(C)) * 100),2) AS "공정율"
FROM (SELECT A.BIZ_REGION
, COUNT(*) AS A
, 0 AS B
, 0 AS C
FROM TB_RTU A
WHERE A.USE_STATUS = 'C'
GROUP BY A.BIZ_REGION
UNION ALL
SELECT A.BIZ_REGION
, 0 AS A
, COUNT(*) AS B
, 0 AS C
FROM TB_RTU A
WHERE A.USE_STATUS = 'G'
GROUP BY A.BIZ_REGION
UNION ALL
SELECT A.BIZ_REGION
, 0 AS A
, 0 AS B
, COUNT(*) AS C
FROM TB_RTU A
WHERE A.USE_STATUS = 'F'
GROUP BY A.BIZ_REGION
) A
INNER JOIN TB_COMMON_CODE B ON A.BIZ_REGION = B.CODE2 AND B.DIVISION = 'BIZ_REGION'
GROUP BY A.BIZ_REGION
) A
ORDER BY 1
위의 쿼리에서 맨 마지막 행에 합계를 구하는 방법이 어떻게 되나요..?
고수님들의 조언 부탁드립니다.
SELECT IFNULL(b.value2, '합계') 지역구분
, a.대상건수
, a.미설치건수
, a.설치건수
, a.고장건수
, a.고장율
, a.공정율
FROM (SELECT biz_region
, COUNT(*) 대상건수
, COUNT(CASE use_status WHEN 'C' THEN 1 END) 미설치건수
, COUNT(CASE use_status WHEN 'G' THEN 1 END) 설치건수
, COUNT(CASE use_status WHEN 'F' THEN 1 END) 고장건수
, ROUND( COUNT(CASE use_status WHEN 'F' THEN 1 END)
/ COUNT(CASE use_status WHEN 'G' THEN 1 END)
* 100, 2) 고장율
, ROUND( COUNT(CASE use_status WHEN 'G' THEN 1 END)
/ COUNT(*)
* 100, 2) 공정율
FROM tb_rtu
WHERE use_status IN ('C', 'G', 'F')
GROUP BY biz_region
WITH ROLLUP
) a
LEFT OUTER JOIN tb_common_code b
ON b.division = 'BIZ_REGION'
AND b.code2 = a.biz_region
ORDER BY CASE b.value2 IS NULL THEN 0 ELSE 1 END
, b.value2
;
) 버튼을 클릭하여 작성 하시면 됩니다.