[Oracle SQL Developer] 기초 IX - 집계 함수

1. 집계 함수(SUM, AVG, COUNT, MIN, MAX) - GROUP BY 절이 사용된다.

---

  1.1. SUM(c)
    1.1.1. 컬럼 c에 저장된 값의 합꼐를 반환한다.
    
    예) 2005년 6월에 판매된 제품의 수량을 합산하여 출력하시오.
    
    SELECT SUM(CART_QTY) 수량
      FROM CART
     WHERE SUBSTR(CART_NO,1,6) = '200506';
    -- GROUP BY 절은 불필요한 경우 1 

    예) 회원테이블에서 회원들의 모든 마일리지 합계를 구하시오.

    SELECT SUM(MEM_MILEAGE) "회원들의 총 마일리지"
      FROM MEMBER;
    -- GROUP BY 절은 불필요한 경우 2  

    1.1.2. 표현식
     : 한개이상의 값과 연산자, 함수 등이 결합된 식      
     CASE WHEN ~ THEN 

 

     1.1.2.1. 사용 형식
       : 조건을 만족하면 값을 변경하고 만족하는 값이 없다면 ELSE 의 값으로 변경된다.
       : 반드시 END로 CASE문을 종료시켜야 한다.

 

     CASE WHEN 조건1 THEN 값1 

          WHEN 조건2 THEN 값2
                  :
          ELSE 값n
      END    
     
     예) 회원테이블에서 두번째 주민등록번호 첫자리 값이 1이면 '남성회원' 2이면 '여성회원'을 나타내시오.
     
     SELECT MEM_ID 회원번호,
            MEM_NAME 이름, 
            MEM_REGNO2 "주민번호 뒷자리",
            CASE WHEN SUBSTR(MEM_REGNO2,1,1) = '1' THEN '남성회원'
                 WHEN SUBSTR(MEM_REGNO2,1,1) = '2' THEN '여성회원'
                 ELSE '오류' END AS 비고
       FROM MEMBER;
     
     
     예) 사원테이블에서 급여를 조회하여 그 값이 5000이하이면 'C등급', 15000~5001이면 'B등급',
         15000 초과이면 'A등급'의 값을 비고에 출력하시오.
     
     SELECT EMPLOYEE_ID 사원번호,
            EMP_NAME 사원명,
            SALARY 급여,
            CASE WHEN SALARY <= 5000 THEN 'C등급' 
                 WHEN /*SALARY > 5000 AND*/ SALARY <= 15000 THEN 'B등급' 
                 ELSE 'A등급' 
            END AS 비고
       FROM EMPLOYEES;
     
       
     예) 회원테이블에서 남,여 회원*별* 마일리지 합계를 구하시오.
     
     SELECT CASE WHEN SUBSTR(MEM_REGNO2,1,1) = '1' THEN '남성회원'
                 WHEN SUBSTR(MEM_REGNO2,1,1) = '2' THEN '여성회원'
                 ELSE '오류' END AS 성별,
             SUM(MEM_MILEAGE) AS "마일리지 합계"
       FROM MEMBER
      GROUP BY SUBSTR(MEM_REGNO2,1,1);
     
     
     예) 2005년 6월 회원별 구매수량 합계를 구하시오.
     
     SELECT CART_MEMBER 회원번호,
            SUM(CART_QTY) "구매수량 합계"
       FROM CART
      WHERE SUBSTR(CART_NO,1,6) LIKE '200506%' 
      GROUP BY CART_MEMBER
      ORDER BY 2 ASC;
        
     예) 2005년 6월 회원별, 날짜별 구매수량 합계를 구하시오.
     SELECT CART_MEMBER AS 회원번호,
            SUBSTR(CART_NO,1,8) AS 날짜,
            SUM(CART_QTY) AS "구매수량 합계"
       FROM CART
      WHERE SUBSTR(CART_NO,1,6) LIKE '200506%'
      GROUP BY CART_MEMBER, SUBSTR(CART_NO,1,8)
      -- GROUP BY 절은 SELECT 이후의 컬럼 조건을 보고 작성해야한다.
      ORDER BY 1 DESC;
      
     예) 2005년 5월 회원별 구매금액 합계를 구하시오.      
         단 ,Alias는 회원번호, 회원명, 구매금액 합계
         
     SELECT CART_MEMBER 회원번호, 
            MEM_NAME 회원명, 
            SUM(CART_QTY * PROD_PRICE) "구매금액 합계"   
       FROM CART, MEMBER, PROD
      WHERE SUBSTR(CART_NO,1,6) = '200505'
        AND MEM_ID = CART_MEMBER
        AND CART_PROD = PROD_ID
      GROUP BY CART_MEMBER, MEM_NAME
      ORDER BY 1;
     
     
     예) 2005년 5월 회원별 구매금액 합계를 구하시오. 구매 금액 합계가 1000만원 이상인 회원만 조회하시오.      
         단 ,Alias는 회원번호, 회원명, 구매금액 합계

     SELECT CART_MEMBER 회원번호, 
            MEM_NAME 회원명, 
            SUM(CART_QTY * PROD_PRICE) "구매금액 합계"   
       FROM CART, MEMBER, PROD
      WHERE SUBSTR(CART_NO,1,6) LIKE '200505%'
        AND MEM_ID = CART_MEMBER
        AND CART_PROD = PROD_ID
      GROUP BY CART_MEMBER, MEM_NAME
     HAVING SUM(CART_QTY * PROD_PRICE) >= 10000000
      ORDER BY 1;
     
     
     
     예) 사원테이블에서 각 부서별 급여합계를 구하시오
        단, Alias는 부서번호, 급여합계이다
 
     SELECT A.DEPARTMENT_ID AS "부서번호",
            B.DEPARTMENT_NAME AS "부서명",
            SUM(A.SALARY) AS "급여 합계"
       FROM EMPLOYEES A, DEPARTMENTS B
      WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
      GROUP BY A.DEPARTMENT_ID, B.DEPARTMENT_NAME
      ORDER BY 1;
      
      
     예) KOR_LOAN_STATUS 테이블에서 2013년 지역별 대출 잔액을 구하시오.
         단, Alias는 지역, 대출잔액 합계
         
     SELECT REGION 지역, 
            TO_CHAR(SUM(LOAN_JAN_AMT),'999,999.0') "대출잔액 합계"
       FROM KOR_LOAN_STATUS
      WHERE SUBSTR(PERIOD,1,4) LIKE '2013%'
      GROUP BY REGION;
      
      
     예) KOR_LOAN_STATUS 테이블에서 2013년 지역별 구분별 대출 잔액을 구하시오.
         단, Alias는 지역, 구분, 대출잔액 합계  
      
     SELECT REGION 지역,
            GUBUN 구분,
            TO_CHAR(SUM(LOAN_JAN_AMT),'999,999.0') "대출잔액 합계"
            -- TO_CHAR(c1,c2) 변환함수
            -- c1의 값을 c2의 모양으로 변환한다.
       FROM KOR_LOAN_STATUS
      WHERE SUBSTR(PERIOD,1,4) LIKE '2013%'
      GROUP BY REGION, GUBUN
      ORDER BY 1;      
      
      
  
  
  1.2. COUNT(c|*)
    1.2.1. 쿼리의 결과로 반환되는 행의 갯수
    
    예) 장바구니테이블에서 2005년 4월 판매건수를 조회하시오.
    
    SELECT COUNT(CART_NO) 판매건수,
           COUNT(*) 판매건수_
      FROM CART
     WHERE CART_NO LIKE '200504%';
     
    예) 장바구니테이블에서 2005년 월별 판매건수를 조회하시오.
    
    SELECT SUBSTR(CART_NO,5,2) 월,
           COUNT(CART_NO) 판매건수,
           COUNT(*) 판매건수_DI
      FROM CART
     WHERE SUBSTR(CART_NO,1,4) LIKE '2005%'
     GROUP BY SUBSTR(CART_NO,5,2)
     ORDER BY 1 ASC;
     
    예) 사원테이블에서 각 부서별 인원수와 급여합계를 구하시오.
        단, Alias는 부서코드, 인원수, 급여합계 이다.
    
    SELECT /*ljm.EMPLOYEES.*/A.DEPARTMENT_ID 부서코드,
           B.DEPARTMENT_NAME 부서명,
           COUNT(A.EMPLOYEE_ID) 인원수,
           SUM(A.SALARY) 급여합계
      FROM EMPLOYEES A, DEPARTMENTS B
     WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
       --AND B.DEPARTMENT_NAME = A.DEPARTMENT_ID
     GROUP BY A.DEPARTMENT_ID, B.DEPARTMENT_NAME
     ORDER BY 1;
     
     
    예) 상품테이블에서 상품분류별 상품의 수를 조회하시오.
       단 Alias는 상품분류코드, 상품의수이다.
       
       
    SELECT PROD_LGU 상품분류코드, 
           COUNT(*) 상품의수
      FROM PROD
     GROUP BY PROD_LGU
     ORDER BY 1;
     
     
    예) 회원테이블에서 회원들이 가지고 있는 취미의 종류수를 조회하시오.
    
    SELECT COUNT(DISTINCT MEM_LIKE)
      FROM MEMBER;
      
 
 
     
  1.3. AVG(c) 
    1.3.1. 컬럼 'c'의 값의 산술평균 값을 반환한다.
    
    
    예) 사원테이블에서 각 부서별 평균급여를 조회하시오.
        Alias는 부서코드, 평균급여이다.
        
    SELECT DEPARTMENT_ID 부서코드,
           ROUND(AVG(NVL(SALARY,0))) 평균급여
      FROM EMPLOYEES
     GROUP BY DEPARTMENT_ID
     ORDER BY 1;
     
    
    
    예) 각 부서의 평균 급여보다 급여를 많이 받는 사원이 존재하는 부서를 조회하시오.
        Alias는 사원코드, 사원명, 부서, 급여
        
    SELECT DEPARTMENT_ID , DEPARTMENT_NAME
      FROM DEPARTMENTS A
     WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                               FROM EMPLOYEES
                              WHERE SALARY > (SELECT AVG(SALARY)
                                                FROM EMPLOYEES));

    
    예) 2005년 5월 평균구매수량보다 많은 상품을 구매한 회원정보를 조회하시오.
        Alias는 회원번호, 회원명, 구매수량
        
    SELECT A.CART_MEMBER 회원번호, 
           B.MEM_NAME 회원명, 
           ROUND(AVG(A.CART_QTY)) 평균구매수량
      FROM CART A, MEMBER B
     WHERE SUBSTR(CART_NO,1,6) LIKE '200505%'
       AND A.CART_MEMBER = B.MEM_ID -- JOIN이 발생하여 공통컬럼을 설정해준다.
     GROUP BY A.CART_MEMBER, B.MEM_NAME 
    HAVING (SELECT AVG(CART_QTY)
             FROM CART 
            WHERE CART_NO LIKE '200505%') <=  ROUND(AVG(A.CART_QTY));
       

    SELECT ROUND(AVG(ALL PROD_COST)) "모든 COST", ROUND(AVG(DISTINCT PROD_COST)) "중복배제 COST"
      FROM PROD;
    
    
    ** NULL 처리함수
    
      1) NVL(c,v)
        . 'c'의 값이 NULL 일 경우 'v'를 반환하고 아니면 'c'값을 반환한다.
        . 보통 숫자컬럼 값을 연산할 때 주로 사용한다.
      
      2) NVL2(c, v1, v2)
        . 'c'의 값이 NULL 일 경우 'v2'를 반환하고, 'c'의 값이 NULL 이 아니면 'v1'을 반환한다.
        . 'c'값이 NULL 여부에 따라 서로 다른 값을 반환해야 하는 경우에 사용한다.
        
      3) IS NULL / IS NOT NULL
        . 조건식에서 특정 컬럼 값이 NULL 인지 판단하는 경우에 사용한다.
        . '컬럼명 = NULL'과 같은 조건식은 NULL 여부 판단이 불가능하다.
        . '컬럼명 = NULL'을 사용하려면 '컬럼명 IS NULL / IS NOT NULL'로 작성한다.
      
      예) 사원테이블에서 COMMISSION_PCT가 NULL이 아닌 사원수를 구하시오.
          단, Alias는 사원수
          
      SELECT COUNT(*) AS 사원수
        FROM EMPLOYEES
       WHERE COMMISSION_PCT IS NOT NULL;
  
  
      예) 사원테이블에서 보너스를 계산하여 출력하시오.
          보너스는 급여 * COMMISSION_PCT 이다.
          
          
      SELECT EMPLOYEE_ID 사원번호,
             EMP_NAME 사원명,
             SALARY * NVL(COMMISSION_PCT,0) AS 보너스,
             SALARY + SALARY * NVL(COMMISSION_PCT,0) 지급액
        FROM EMPLOYEES -- 모든 사원들 중 보너스가 있는 사원들 출력하는 경우
       ORDER BY 4 DESC;
       WHERE COMMISSION_PCT IS NOT NULL; -- 보너스가 있는 사원만 출력하는 경우
       

  
  
  1.4. MAX(c)
    1.4.1. 컬럼 'c'의 값 중 최대값을 반환한다.
      - 정렬 후 최대값을 찾으므로, 누적된 자료의 값이 많을수록 처리 속도에 부담이 간다.
    1.4.2. 수행 방법 
      - 범위 내의 컬럼값을 정렬한 후 결과값을 반환한다.
    1.4.3. INDEX를 활용  
    
       
    
    
  1.5. MIN(c)
    1.5.1. 컬럼 'c'의 값 중 최소값을 반환한다.
      - 정렬 후 최소값을 찾으므로, 누적된 자료의 값이 많을수록 처리 속도에 부담이 간다.
    1.5.2. 수행 방법 
      - 범위 내의 컬럼값을 정렬한 후 결과값을 반환한다.
    1.5.3. INDEX를 활용  
    
    
    예) 상품테이블에서 각 분류별 최고값 상품과 최저가 상품을 조회하시오.
        Alias는 분류코드, 최고가, 최저가
    
    SELECT PROD_LGU 분류코드, 
           MAX(PROD_PRICE) 최고가,
           MIN(PROD_PRICE) 최저가
      FROM PROD
     GROUP BY PROD_LGU
     ORDER BY 1;
     
     
     
     
    SELECT B.PROD_LGU 분류,
           B.PROD_ID 상품번호,
           B.PROD_NAME 상품명,
           B.PROD_PRICE 가격
      FROM PROD B, (SELECT PROD_LGU, MAX(PROD_PRICE) AS MP
                    FROM PROD
                   GROUP BY PROD_LGU) A
     WHERE A.PROD_LGU = B.PROD_LGU
       AND B.PROD_PRICE = A.MP
     ORDER BY 1;
    
    
    
    예) 사원테이블에서 급여가 가장많은 사람과 가장 적은 사람을 조회하시오.
    
    SELECT --EMPLOYEE_ID 사원코드, 
           --EMP_NAME 사원명,
           MAX(SALARY) 최고급여, 
           MIN(SALARY) 최저급여
      FROM EMPLOYEES;
     --GROUP BY EMPLOYEE_ID, EMP_NAME
     --ORDER BY 1;
    
    
    
    예) 사원테이블에서 급여가 가장많은 사람과 가장 적은 사람을 조회하시오.
        부서코드, 최대급여, 최소급여 
         
    SELECT B.DEPARTMENT_ID 부서코드, 
           MAX(B.SALARY) 최대급여, 
           MIN(B.SALARY) 최소급여     
      FROM /*DEPARTMENTS A,*/ EMPLOYEES B
     GROUP BY B.DEPARTMENT_ID
     ORDER BY 1;