[Oracle] SQL FUNCTION_06 : 집계 함수

6. 집계 함수

 

SUM() (변수를 더함)
AVG() (컬럼의 평균을 구함)
COUNT() (행 수를 반환)
MAX, MIN (각각 최대값, 최소값을 반환)
ROLLUP() (다양한 집계를 반환 보통 : 각 그룹의 총액을 계산시 사용)
CUBE() (주어진 컬럼의 조합 경우의 수 반환 특수한 경우가 아니라면 2^n 개 이상 나온다.)

 

 

    1. SUM(), AVG(), COUNT(), MAX, MIN

    - 특정 컬럼을 기준으로 같은 값을 갖는 행들을 그룹으로 묶고 각 그룹마다 
      합, 평균, 갯수 등을 구하는 함수
    - 집계함수는 다른 집계함수를 포함할 수 없음
    
    (사용형식)

        SELECT 컬럼1,...컬럼n
               SUM(컬럼명) | AVG(컬럼명) | COUNT(*|컬럼명) | MAX(컬럼명) | MIN(컬럼명)
          FROM 테이블명
        [WHERE 조건]
         GROUP BY 컬럼1[,...컬럼n]
       [HAVING 조건]
        [ORDER BY 컬럼명|컬럼인덱스 [ASC|DESC],...]

        . SELECT 절에 집계함수만 사용되면 GROUP BY절 생략
        . GROUP BY 절에는 SELECT 절에 사용된 일반 컬럼을 모두 다 기술해야 함
        . GROUP BY 절에 하나 이상의 컬럼이 기술되면 가장 왼쪽에 기술된 컬럼을 기준으로
                   그룹을 묶고(대분류), 다음에 기술된 컬럼으로 각 그불 내에서 다시 그룹(중분류)을 묶는다
        . HAVING 조건은 집게함수에 대한 조건을 부여할때 반드시 사용
        . 기술순서 : WHERE → GROUP BY → HAVING → ORDER BY

 

 

WHERE - GROUP BY

HAVING - ORDER BY

 

사용예) 사원테이블에서 모든 사원수를 조회하시오

           SELECT COUNT(*) AS 사원수,
                  ROUND(AVG(SALARY)) AS 평균급여,
                  SUM(SALARY) AS 급여합계,
                  MAX(SALARY) AS 최고급여액,
                  MIN(SALARY) AS 최저급여액
             FROM HR.EMPLOYEES;

        
    사용예) 상품테이블에서 모든 상품의 수를 조회하시오

           SELECT COUNT(PROD_ID) AS "상품의 수"
             FROM PROD;

        
    사용예) 회원테이블에서 회원수를 조회하시오

           SELECT COUNT(MEM_ID) AS 회원수
             FROM MEMBER;

        
    사용예) 사원테이블에서 부서별 사원수 평균급여를 조회하시오.

           SELECT DEPARTMENT_ID AS 부서코드,
                  COUNT(*) AS 사원수,
                  ROUND(AVG(SALARY)) AS 평균급여
             FROM HR.EMPLOYEES
            GROUP BY DEPARTMENT_ID
            ORDER BY 1;

    
    사용예) 상품테이블에서 분류별 매출가가 10만원 이상인 상품의 수를 조회하시오

           SELECT PROD_LGU AS 분류코드,
                  COUNT(*) AS "상품의 수"
             FROM PROD
            WHERE PROD_PRICE>=100000
            GROUP BY PROD_LGU
            ORDER BY 1;

                
    시용예) 매입테이블에서 2020년 월별 매입수량합계를 조회하시오ㅓ

           SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월별,
                  SUM(BUY_QTY) AS 매입수량합계
             FROM BUYPROD
            WHERE EXTRACT(YEAR FROM BUY_DATE)=2020
            GROUP BY EXTRACT(MONTH FROM BUY_DATE)
            ORDER BY 1;

            
            
    시용예) 매입테이블에서 2020년 제품별 매입수량합계를 조회하시오

           SELECT BUY_PROD AS 상품번호,
                  SUM(BUY_QTY) AS 매입수량합계
             FROM BUYPROD
            WHERE EXTRACT(YEAR FROM BUY_DATE)=2020
            GROUP BY BUY_PROD
            ORDER BY 1

; 
            
    시용예) 매입테이블에서 2020년 제품별 매입수량합계가 100개 이상인 상품만  조회하시오

           SELECT BUY_PROD AS 상품번호,
                  SUM(BUY_QTY) AS 매입수량합계
             FROM BUYPROD
            WHERE EXTRACT(YEAR FROM BUY_DATE)=2020
            GROUP BY BUY_PROD
           HAVING SUM(BUY_QTY) >=100
            ORDER BY 1 ;
                --HAVING : 집계합수 조건에 쓰임

    
    시용예) 매입테이블에서 2020년 월별 상품별 매입수량합계를 조회하시오

           SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월별,
                  BUY_PROD AS 상품번호,
                  SUM(BUY_QTY) AS 매입수량합계
             FROM BUYPROD
            WHERE EXTRACT(YEAR FROM BUY_DATE)=2020
            GROUP BY EXTRACT(MONTH FROM BUY_DATE), BUY_PROD
            ORDER BY 1, 3 DESC ;

 

 

 사용예) 회원테이블에서 성별 평균 마일리지를 조회하시오

           SELECT CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN ('1','3') THEN '남성'
                  ELSE '여성' END AS 성별,
                       
                  ROUND(AVG(MEM_MILEAGE)) AS "평균 마일리지"
             FROM MEMBER
            GROUP BY CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN ('1','3') THEN '남성'
                     ELSE '여성' END;

        
    사용예) 2020년 4-7월 회원별 평균 구매 횟수를 조회하시오

             SELECT A.CART_MEMBER AS 회원번호,
                    COUNT(*) AS 구매횟수
               FROM (SELECT CART_MEMBER,CART_NO, COUNT(*)
                       FROM CART
                      WHERE SUBSTR(CART_NO,1,6) BETWEEN '202004' AND '202007'
                      GROUP BY CART_MEMBER, CART_NO)A
              GROUP BY  A.CART_MEMBER
              ORDER BY 1;

            
    
    사용예) 2020년 5월 회원별 구매금액 합계를 구하여 출력하되 구매금액이
                 500만원 이상인 자료만 조회하시오.
                 Alias는 회원번호,회원명,구매금액

           SELECT B.MEM_ID AS 회원번호,
                  B.MEM_NAME AS 회원명,
                  SUM(A.CART_QTY*C.PROD_PRICE) AS 구매금액
             FROM CART A, MEMBER B, PROD C
            WHERE A.CART_NO LIKE '202005%'
              AND A.CART_MEMBER=B.MEM_ID
              AND A.CART_PROD=C.PROD_ID
            GROUP BY B.MEM_ID, B.MEM_NAME
           HAVING SUM(A.CART_QTY*C.PROD_PRICE)>=5000000
            ORDER BY 3 DESC;

 

    사용예) 장바구니테이블에서 2020년 5월 회원별 구매금액을 구하되 구매금액이
                 많은 5명을 조회하시오
                 Alias는 회원번호, 회원명, 구매금액

        SELECT A.A1 AS 회원번호, 
               A.A2 AS 회원명, 
               A.A3 AS 구매금액
          FROM (SELECT B.MEM_ID AS A1,
                       B.MEM_NAME AS A2,
                       SUM(A.CART_QTY*C.PROD_PRICE) AS A3
                  FROM CART A, MEMBER B, PROD C
                 WHERE A.CART_NO LIKE '202005%'
                   AND A.CART_MEMBER=B.MEM_ID
                   AND A.CART_PROD=C.PROD_ID
                 GROUP BY B.MEM_ID, B.MEM_NAME
                 ORDER BY 3 DESC)A
         WHERE ROWNUM<=5;

 

 

ROLLUP 과 CUBE

    - GROUP BY 절에서만 사용되어 다양한 집계결과를 반환
    
 1. ROLLUP
 (사용형식)
 
    GROUP BY ROLLUP (컬럼명1[,컬럼명2,...컬럼명n])[,컬럼명,...]
        - 사용된 컬럼명n~ 컬럼명1이 모두 적용된 집계를 반환한 후
          컬럼명n부터 하나씩 제거한 집계를 반환함
        - 마지막은 모든 컬럼이 적용되지 않은 집계(전체집계)를 반환
        - ROLLUP 절에 기술된 컬럼의 수가 n개일때 n+1개의 집계반환
        - ROLLUP 절 전, 후에 컬럼이 올수구가 있으면 이름 '부분 ROLLUP'이라 함
        
  사용예) 2020년 월별, 회원별, 제품별 구매수량 합계를 조회하시오
         Alias는 월, 회원번호, 상품번호, 구매수량합계

 

  (ROLLUP 절 사용)

         SELECT SUBSTR(CART_NO,5,2) AS 월, 
                CART_MEMBER AS 회원번호, 
                CART_PROD AS 상품번호,
                SUM(CART_QTY) AS 구매수량합계
           FROM CART
          WHERE CART_NO LIKE '2020%'
          GROUP BY SUBSTR(CART_NO,5,2),CART_MEMBER,CART_PROD
          ORDER BY 1, 2, 3;

          
  (CUBE 절 사용)

	 SELECT SUBSTR(CART_NO,5,2) AS 월, 
                CART_MEMBER AS 회원번호, 
                CART_PROD AS 상품번호,
                SUM(CART_QTY) AS 구매수량합계
           FROM CART
          WHERE CART_NO LIKE '2020%'
          GROUP BY CUBE(SUBSTR(CART_NO,5,2),CART_MEMBER,CART_PROD)
          ORDER BY 1, 2, 3;