[8i] 그룹함수를 사용한 데이터 집계 예

/********************************************************************************************
-- Title : [8i] 그룹함수를 사용한 데이터 집계 예
-- Reference : OLN
-- Key word :
********************************************************************************************/

/****************************************************************************************
-- AVG, MAX, MIN, SUM 함수 사용 예
****************************************************************************************/
SQL> SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)
  2  FROM emp
  3  WHERE job LIKE 'SALES%';

  AVG(SAL)   MAX(SAL)   MIN(SAL)   SUM(SAL)
---------- ---------- ---------- ----------
      1400       1600       1250       5600
     
     
/****************************************************************************************
-- COUNT 함수의 사용 예
****************************************************************************************/
SQL> SELECT COUNT(*)
  2  FROM emp
  3  WHERE deptno = 30;

  COUNT(*)
----------
         6
   
SQL> SELECT COUNT(comm)
  2  FROM emp
  3  WHERE deptno = 30;

  COUNT(comm)
----------
         4
        
        
/****************************************************************************************
-- GROUP BY 절 사용 예
****************************************************************************************/
SQL> SELECT deptno, AVG(sal)
  2  FROM emp
  3  GROUP BY deptno;

    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667
        20       2175
        30 1566.66667
       

/****************************************************************************************
-- 다중 열에서 GROUP BY 절 사용
****************************************************************************************/
SQL>SELECT deptno, job, SUM(sal)
  2  FROm emp
  3  GROUP BY deptno, job;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600

9 rows selected.

/****************************************************************************************
-- HAVING 절 사용 예
****************************************************************************************/
SQL> SELECT job, SUM(sal) PAYROLL
  2  FROM emp
  3  WHERE job NOT LIKE 'SALES%'    
  4  GROUP BY job
  5  HAVING SUM(sal) > 5000
  6  ORDER BY SUM(sal);

JOB          PAYROLL
--------- ----------
ANALYST         6000
MANAGER         8275