[SQL] 백분율 예제

목차

SELECT 5 / 30 * 100 
  FROM dual
;

1. 백분율 계산식

구하려는 컬럼을 전체 컬럼개수로 나눈 뒤 100을 곱한다.

---

실전 SQL(cubrid 사용)

SELECT CASE WHEN A.HOUSE_TY_SECD = '1' THEN '다가구'
			WHEN A.HOUSE_TY_SECD = '2' THEN '빌라'
			WHEN A.HOUSE_TY_SECD = '3' THEN '아파트'
			WHEN A.HOUSE_TY_SECD = '4' THEN '오피스텔'
			WHEN A.HOUSE_TY_SECD = '5' THEN '단독'
			WHEN A.HOUSE_TY_SECD = '6' THEN '다세대'
			WHEN A.HOUSE_TY_SECD = '7' THEN '연립'
			WHEN A.HOUSE_TY_SECD = '8' THEN '기타'
		END AS houseType
      , COUNT(A.HOUSE_TY_SECD) AS totalCnt
      , CASE WHEN (SELECT count(*)
      				 FROM BOARD
      				WHERE INFO_SECD = '30' 
					) = 0 
	  THEN 0
	  ELSE ROUND(COUNT(A.HOUSE_TY_SECD) / CAST((SELECT count(*)
      				 				   FROM BOARD
      				                  WHERE INFO_SECD = '30'
								) AS float) * 100, 2)
	  END AS rate								
  FROM BOARD A
 WHERE A.INFO_SECD = '30' 
 GROUP BY A.HOUSE_TY_SECD
;

함수(RATIO_TO_REPORT())

SELECT
    type,
    COUNT(*) AS count,
    ROUND(RATIO_TO_REPORT(COUNT(type)) OVER () * 100, 2) AS percentage
FROM
    BOARD
GROUP BY
    type;

개인 스터디 기록을 메모하는 공간이라 틀린점이 있을 수 있습니다.

틀린 점 있을 경우 댓글 부탁드립니다.