Oracle(2022.02.18)-AVG,COUNT,MAX,MIN

2)AVG
-그룹별 평균값을 반환
 (사용형식)
 AVG(expr)
 

 사용예)상품테이블에서 분류별 평균 매입가를 조회하시오.
  SELECT PROD_LGU AS 분류코드,
         ROUND(AVG(PROD_COST)) AS "평균 매입가"
    FROM PROD
    GROUP BY PROD_LGU
    ORDER BY 1;

사용예)사원테이블에서 부서별 평균 근속년수를 조회하시오.
   SELECT  DEPARTMENT_ID AS 부서코드,
           ROUND(AVG(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE)),1) AS "평균 근속년수"
    FROM HR.EMPLOYEES
    GROUP BY DEPARTMENT_ID
    ORDER BY 1;

    

사용예)사원테이블에서 부서별 평균급여를 조회하시오.
    SELECT DEPARTMENT_ID AS 부서코드,
           ROUND(AVG(SALARY)) AS 평균급여
        FROM HR.EMPLOYEES
        GROUP BY DEPARTMENT_ID
        ORDER BY 1;

사용예) 회원테이블에서 년령대별 평균마일리지값을 구하시오
    SELECT TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)||'대' AS 년령대,
           ROUND(AVG(MEM_MILEAGE)) AS 평균마일리지
      FROM MEMBER
      GROUP BY TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)
      ORDER BY 1;

설명) 만약에 평균마일리지가 제일 큰값을 구하시오 MAX와 AVG는 같이 쓰일 수 없다. 즉 집계함수와 집계함수는 같이 사용X

---

3)COUNT
  -그룹별 자료의 수(행의 수)를 반환
  (사용형식)
  COUNT([컬럼명|*]) 
   .'컬럼명|*': 외부조인(OUTER JOIN)을 제외하고 '컬럼명'이나 '*'는 차이가 없음
   .외부조인인 경우 '*'를 사용하면 자료가 없어도 '1'을 출력함(NULL값을 갖는 행이 존재하기 때문)
    따라서 외부조인에서 COUNT 함수 사용시 '컬럼명'을 기본키 컬럼으로 기술하는 것이 안전하다.

사용예)사원테이블에서 부서별 인원수를 조회하시오.
    SELECT  DEPARTMENT_ID AS 부서코드,
        COUNT(*) AS 인원수1, -->이름은 존재하므로 1이나왔음
        COUNT(EMP_NAME) AS 인원수2,   
        COUNT(DEPARTMENT_ID) AS 인원수3,  --> 부서코드로 COUNT를 하면 NULL값이 있어서 0
        COUNT(COMMISSION_PCT) AS 인원수4
    FROM HR.EMPLOYEES
    GROUP BY DEPARTMENT_ID
    ORDER BY 1;

NULL값 존재

사용예)HR계정 회사의 모든 부서별 인원수를 조회하시오.
  -사원테이블에서 사용하는 부서
    SELECT DISTINCT DEPARTMENT_ID
      FROM HR.EMPLOYEES
      ORDER BY 1;
      
  -부서테이블에 존재하는 부서 
    SELECT  DEPARTMENT_ID
      FROM HR.DEPARTMENTS
      ORDER BY 1;   -->NULL값이 존재할 수 없다. 왜냐 기본키이기 때문

1.

      ------------------------------------------------------
   

(+)더 적은쪽에 쓰이며 아웃터조인 :더 많은 행을 가진 테이블과 대응되게 해준다. 하지만 테이블 값은 NULL이고 *를 사용하면 NULL값도 세어준다.
     아웃터조인 즉 외부조인할때는 집계함수에 기본키를 써주는 것이 좋다
     SELECT  B.DEPARTMENT_ID AS 부서코드,
        COUNT(*) AS 인원수1, -->이름은 존재하므로 1이나왔음
        COUNT(A.EMP_NAME) AS 인원수2  
    FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
    WHERE A.DEPARTMENT_ID(+)=B.DEPARTMENT_ID
    GROUP BY B.DEPARTMENT_ID
    ORDER BY 1;

만약에 SELECT  A.DEPARTMENT_ID AS 부서코드, -->이부분을 A로 하게되면 
        COUNT(*) AS 인원수1, -->이름은 존재하므로 1이나왔음
        COUNT(A.EMP_NAME) AS 인원수2  
    FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
    WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID(+) -->A를 기준으로
    GROUP BY A.DEPARTMENT_ID
    ORDER BY 1;  
    

사용예)사원테이블에서 부서별 인원수가 5인 이상인 부서조회
-->~별 조건 즉 그룹별 조건은 HAVING절에 기술
    SELECT  DEPARTMENT_ID AS 부서코드,
        COUNT(*) AS 인원수
    FROM HR.EMPLOYEES
    GROUP BY DEPARTMENT_ID
    HAVING COUNT(*)>=5
    ORDER BY 1;

  

사용예)상품테이블에서 분류별 상품 수를 조회하시오.
    SELECT PROD_LGU AS 분류코드,
           COUNT(*) AS "상품 수"
        FROM PROD
        GROUP BY PROD_LGU
        ORDER BY 1;

사용예)매입테이블에서 월별 매입건수를 조회하시오.
    SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월,
           COUNT(BUY_QTY) AS 매입건수
        FROM BUYPROD
        GROUP BY EXTRACT(MONTH FROM BUY_DATE)
        ORDER BY 1;

    

사용예)매출테이블에서 2005년도 월별매출건수가 10이상인 매출 정보만 조회하시오.
(CART, 한사람이 여러상품을 구매했어도 카드하나로 구매한 것을 매출건수 1로 보겠다. )
    SELECT SUBSTR(CART_NO,5,2) AS 월,
           COUNT(*) AS 매출건수
      FROM CART
      WHERE CART_NO LIKE '2005%'
      GROUP BY SUBSTR(CART_NO,5,2)
      HAVING COUNT(*)>=10
      ORDER BY 1;
      --->다 다른 매출로 본경우임

수정 후 
   SELECT SUBSTR(A.CNO,5,2)||'월' AS 월,
        COUNT(*) AS 매출건수
    FROM( SELECT DISTINCT CART_NO AS CNO
        FROM CART) A  -->중복되지 않은 CART_NO을 서브쿼리로 해야 매출건수1
        GROUP BY SUBSTR(A.CNO,5,2)
        HAVING COUNT(*)>=10
        ORDER BY 1;

---

 4)MAX,MIN
  - 제시된 컬럼 값 중 최대(MAX), 최소(MIN) 값을 반환
  (사용형식)
  MAX(expr)/MIN(expr)

사용예) 사원테이블에서 부서별 최대급여와 최소급여를 조회하시오.
   SELECT DEPARTMENT_ID AS 부서,
          MAX(SALARY) AS 최대급여,
          MIN(SALARY) AS 최소급여
      FROM HR.EMPLOYEES
      GROUP BY DEPARTMENT_ID
      ORDER BY 1;

SELECT DEPARTMENT_ID AS 부서,
          EMP_NAME AS 사원명,
          MAX(SALARY) AS 최대급여,
          MIN(SALARY) AS 최소급여
      FROM HR.EMPLOYEES
      GROUP BY DEPARTMENT_ID,EMP_NAME
      ORDER BY 1;
    -->부서코드로 그룹을 묶고 이름으로 다시 묶기 때문에 107개가 나온다. 결국 한사람마다 한개의 그룹이 만들어짐

1~92데이터 생략

사용예) 사원테이블에서 부서별 최대급여를 받는 사원의 사원번호, 사원명, 부서코드, 급여를 조회하시오.
    SELECT
           B.EMPLOYEE_ID AS 사원번호,
           B.EMP_NAME AS 사원명,
           A.DEPARTMENT_ID AS 부서코드,
           A.MSAL AS 급여
        FROM (SELECT DEPARTMENT_ID ,
                     MAX(SALARY) AS MSAL
                FROM HR.EMPLOYEES
                GROUP BY DEPARTMENT_ID) A,
                HR.EMPLOYEES B
        WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
          AND B.SALARY=A.MSAL
       ORDER BY 3;

 

->누군지 모르기 때문에 이렇게 데이터 나온애들의 사원번호, 사원명,부서코드, 급여를 따로 해주어야 한다.

사용예) 2005년 5월 최대구매(금액 기준)를 한 회원의 회원번호,회원명, 구매금액을 조회하시오.
    1. 2005년 5월 회원별 구매금액합계 계산(서브쿼리)
       SELECT A.CART_MEMBER AS ACID,
              SUM(A.CART_QTY*B.PROD_PRICE) AS ASUM
        FROM CART A, PROD B
       WHERE A. CART_PROD=B.PROD_ID --제품코드가 같아야함 (조인조건)
          AND CART_NO LIKE '200505%'
        GROUP BY A.CART_MEMBER;
 
     2. '1'번의 결과에서 'ASUM'값이 제일 큰값을 조회  
     SELECT F.MEM_ID AS 회원번호,
            F.MEM_NAME AS 회원명, 
            D.MCASUM AS 구매금액
        FROM(SELECT MAX(C.ASUM) AS MCASUM
            FROM(SELECT A.CART_MEMBER AS ACID,
                   SUM(A.CART_QTY*B.PROD_PRICE) AS ASUM
                FROM CART A, PROD B
               WHERE A. CART_PROD=B.PROD_ID
                 AND CART_NO LIKE '200505%'
                GROUP BY A.CART_MEMBER) C) D,
        (SELECT A.CART_MEMBER AS ACID,
                   SUM(A.CART_QTY*B.PROD_PRICE) AS ASUM
                FROM CART A, PROD B
               WHERE A. CART_PROD=B.PROD_ID
                 AND CART_NO LIKE '200505%'
                GROUP BY A.CART_MEMBER)E,
            MEMBER F
    WHERE D.MCASUM=E.ASUM   -->제일 큰 구매 금액 같은것을 찾은것임 
      AND F.MEM_ID=E.ACID;

 

1번 결과

2번 결과