[DBMS/Oracle] 그룹함수

[DBMS/Oracle] 그룹함수

그룹함수 

  - 테이블에서 특정컬럼을 기준으로 같은 값을 갖는 자료들로 묶어놓은 집합을 그룹이라함
  - 이 그룹안에서 합계(SUM),평균(AVG),자료의 수(COUNT), 최대/최소 값(MAX/MIN)을 반환해주는 함수
  - 다중행 함수임

  - 집계함수는 집계함수를 포함할 수 없음(단, 일반함수는 집계함수를 포함할 수 있고 집계함수는 일반함수를 포함할 수 있음)

  (사용형식)
    SELECT [컬럼명1 [AS 컬럼별칭1 ,]
                    :
            SUM(col)|AVG(col)|COUNT(*|col)|MAX(col)|MIN(col) [,]
                    :
            [컬럼명n [AS 컬럼별칭n ,]
      FROM 테이블명
     [WHERE 조건] -- 일반 조건
     [GROUP BY 컬럼명[,컬럼명,...]]
    [HAVING 조건] 
     [ORDER BY 컬럼명|컬럼인덱스 [ASC|DESC],...]

     . SELECT 절에 그룹함수가 아닌 일반컬럼이 기술되면 반드시 GROUP BY절을 기술해야함
       SELECT 절에 그룹함수만 있으면 GROUP BY절은 생략 가능
     . GROUP BY 절에는 SELECT절에 기술된 일반컬럼은 반드시 모두 기술해야함.
       SELECT절에 기술되지 않은 컬럼도 필요하면 기술 가능
     . 그룹함수에 조건이 부여되면 반드시 HAVING절에 기술해야 함
     . GROUP BY 절은 WHERE절 다음에 기술하고, HAVING절은 GROUP BY절 다음에 기술해야함(순서 엄수)

 

사용예) 사원테이블에서 전체사원수,전체평균임금,임금합계를 조회하시오.
    SELECT COUNT(*) AS 전체사원수,
           ROUND(AVG(SALARY)) AS 전체평균임금,
           SUM(SALARY) AS 임금합계
      FROM HR.EMPLOYEES

 

사용예) 사원테이블에서 부서별 사원수, 평균임금, 임금합계,최대임금,최소임금을 조회하시오. 
    SELECT DEPARTMENT_ID AS 부서코드,
           COUNT(*) AS 사원수, 
           ROUND(AVG(SALARY)) AS 평균임금, 
           SUM(SALARY) AS 임금합계,
           MAX(SALARY) AS 최대임금,
           MIN(SALARY) AS 최소임금
      FROM HR.EMPLOYEES
     GROUP BY DEPARTMENT_ID
     ORDER BY 1;

 

사용예) 매입테이블에서 2020년 1월 전체 매입금액 합계를 조회하시오.
    SELECT COUNT(*) AS 매입건수,
           SUM(BUY_QTY*BUY_COST) AS "매입금액 합계"
      FROM BUYPROD
     WHERE BUY_DATE BETWEEN TO_DATE(20200101) AND TO_DATE('20200131');

 

사용예) 매입테이블에서 2020년 제품별 매입수량 합계와 매입금액 합계, 매입건수를 조회하시오.
    SELECT BUY_PROD AS 상품코드,
           SUM(BUY_QTY) AS "매입수량 합계",
           SUM(BUY_QTY*BUY_COST) AS "매입금액 합계",
           COUNT(*) AS 매입건수
      FROM BUYPROD
     WHERE EXTRACT(YEAR FROM BUY_DATE)=2020
     GROUP BY BUY_PROD
     ORDER BY 1;

 

사용예) 매입테이블에서 2020년 월별 매입금액 합계, 매입건수를 조회하시오.
    SELECT EXTRACT(MONTH FROM BUY_DATE)||'월' AS 월,
           SUM(BUY_QTY*BUY_COST) AS "매입금액 합계",
           COUNT(*) AS 매입건수
      FROM BUYPROD
     WHERE EXTRACT(YEAR FROM BUY_DATE)=2020
     GROUP BY EXTRACT(MONTH FROM BUY_DATE)
     ORDER BY 1;

 

사용예) 2020년 상반기 제품별 매출수량합계와 매출금액 합계를 조회하시오.
    SELECT CART_PROD AS 상품코드,
           SUM(CART_QTY) AS 매출수량합계,
           SUM(CART_QTY*PROD_PRICE) AS "매출금액 합계"
      FROM CART, PROD 
     WHERE CART_PROD=PROD_ID
       AND SUBSTR(CART_NO,1,6) BETWEEN '202001' AND '202006'
     GROUP BY CART_PROD
     ORDER BY 1;

    SELECT A.CART_PROD AS 상품코드,
           SUM(A.CART_QTY) AS 매출수량합계,
           SUM(A.CART_QTY*PROD_PRICE) AS "매출금액 합계"
      FROM CART A, PROD B
     WHERE CART_PROD=PROD_ID
       AND SUBSTR(A.CART_NO,1,6) BETWEEN '202001' AND '202006'
     GROUP BY A.CART_PROD
     ORDER BY 1;

 

사용예) 매입테이블에서 2020년 월별 매입금액 합계, 매입건수를 조회하되 매입금액 합계가 1억 이상인 월만 조회하시오.
    SELECT EXTRACT(MONTH FROM BUY_DATE)||'월' AS 월,
           SUM(BUY_QTY*BUY_COST) AS "매입금액 합계",
           COUNT(*) AS 매입건수
      FROM BUYPROD
     WHERE EXTRACT(YEAR FROM BUY_DATE)=2020
     GROUP BY EXTRACT(MONTH FROM BUY_DATE)
    HAVING SUM(BUY_QTY*BUY_COST) >= 100000000
     ORDER BY 1;

 

사용예) 매입테이블에서 2020년 월별 매입금액 합계, 매입건수를 조회하되 
        매입금액 합계가 가장 많은 월의 매입 금액 합계, 매입건수를 출력하시오.
  SELECT A.AMON||'월' AS 월,   
           A.ASUM"매입금액 합계",
           A.ACNT"매입건수 합계"
    FROM(SELECT EXTRACT(MONTH FROM BUY_DATE) AS AMON,
                SUM(BUY_QTY*BUY_COST) AS ASUM,
                COUNT(*) AS ACNT
           FROM BUYPROD
          WHERE EXTRACT(YEAR FROM BUY_DATE)=2020
          GROUP BY EXTRACT(MONTH FROM BUY_DATE)
          ORDER BY 2 DESC)A
    WHERE ROWNUM=1;

 

사용예) 2020년 상반기 월별 제품별 매출수량합계와 매출금액 합계를 조회하시오.
    SELECT SUBSTR(A.CART_NO,5,2)||'월' AS 월,
           A.CART_PROD AS 상품코드,
           SUM(A.CART_QTY) AS 매출수량합계,
           SUM(A.CART_QTY*B.PROD_PRICE) AS "매출금액 합계"
      FROM CART A, PROD B
     WHERE A.CART_PROD=B.PROD_ID
       AND SUBSTR(A.CART_NO,1,6) BETWEEN '202001' AND '202006'
     GROUP BY SUBSTR(A.CART_NO,5,2), A.CART_PROD
     ORDER BY 1, 2;

 

 

사용예) 회원테이블에서 성별 평균 마일리지를 조회하시오.
-- 방법1    
    SELECT CASE WHEN SUBSTR(MEM_REGNO2,1,1)='1' OR
                     SUBSTR(MEM_REGNO2,1,1)='3' THEN 
                     '남성회원'
                ELSE 
                     '여성회원'
           END AS 구분,
           ROUND(AVG(MEM_MILEAGE)) AS "평균 마일리지"
      FROM MEMBER
      GROUP BY CASE WHEN SUBSTR(MEM_REGNO2,1,1)='1' OR
                         SUBSTR(MEM_REGNO2,1,1)='3' THEN 
                         '남성회원'
                    ELSE 
                         '여성회원'
                END;

사용예) 회원테이블에서 성별 평균 마일리지를 조회하시오.
-- 방법2
    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;

 

사용예) 회원테이블에서 연령대별 평균마일리지를 조회하시오.
    SELECT TRUNC(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR),-1)||'대' AS 연령대,
           COUNT(*) AS 회원수,
           ROUND(AVG(MEM_MILEAGE)) AS "평균 마일리지"
      FROM MEMBER
     GROUP BY TRUNC(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM MEM_BIR),-1)
     ORDER BY 1;