10. Chapter5 Advanced Query(Aggregation)(20081030)

함수의 종류

• single row 함수 - 값을 하나 받아서 하나의 결과를 출력, 각 row에 대해서 각각 결과가 나옴

예 : select abs(sal) from emp;

1. SQL> SELECT ABS(sal) FROM emp;
2.  
3.   ABS(SAL)
   ----------
          800
         1600
         1250
         2975
         1250
         2850
         2450
         3000
         5000
         1500
         1100
          950
         3000
         1300
4. 14 rows selected.

 

• multi row 함수(Aggregate) - 여러 값을 받아서 하나의 결과를 출력

1. SQL> SELECT SUM(sal) FROM emp;
2.  
3.   SUM(SAL)
   ----------
        29025

 

 

Aggregate Function (P5-1)

1. SQL> SELECT SUM(sal) FROM emp
     2  GROUP BY deptno;
2.  
3.   SUM(SAL)
   ----------
         8750
        10875
         9400

 

 

Aggregate Function(cont.) (P5-3)

• COUNT() 함수의 차이

// (*) -> 모든 컬럼의 갯수 카운트

SELECT count(*) FROM s_emp;

 

 // (컬럼명) -> 해당 컬럼에서 null 값인 경우는 카운트에서 제외

SELECT count(sommission_pct) FROM s_emp;

 

 // (DISTINCT 컬럼명) -> null 값 제외해서 카운트한 것중 중복된 것까지 카운트에서 제외

SELECT count(DISTINCT commission_pct) FROM s_emp;

 

• AVG() 함수 - (1, 3번이 동일한 결과)

(1) SELECT avg(commission_pct) FROM emp;

(2) SELECT sum(commission_pct) / count(*) FROM s_emp;

(3) SELECT sum(commission_pct) / count(commission_pct) FROM s_emp;

 

 

GROUP BY Clause (P5-4)

• 직종별 최대봉급과 최소봉급(job으로 그룹화하고 각 그룹의 max, min 급여 출력)

1. SQL> SELECT job, MAX(sal), MIN(sal)
     2  FROM emp
     3  GROUP BY job;
2.  
3. JOB         MAX(SAL)   MIN(SAL)
   --------- ---------- ----------
   ANALYST         3000       3000
   CLERK           1300        800
   MANAGER         2975       2450
   PRESIDENT       5000       5000
   SALESMAN        1600       1250

 

• GROUP BY 절은 WHERE절 뒤에 나와야함
• ORDER BY 절은 가장 마지작에 나와야함
• ORDER BY 2 = 2번째 컬럼을 기준으로 정렬
• SELECT문에서 Alias를 주면 ORDER BY절에서 해당 Alias 사용가능

1. SQL> SELECT deptno, SUM(sal), AVG(sal)
     2  FROM emp
     3  WHERE hiredate >'81/01/01'
     4  GROUP BY deptno
     5  ORDER BY 2;
2.  
3.     DEPTNO   SUM(SAL)   AVG(SAL)
   ---------- ---------- ----------
           10       8750 2916.66667
           30       9400 1566.66667
           20      10075    2518.75

 

• GROUP BY 절을 쓸때 SELECT절에는 Aggregation 함수나 GROUP BY 절에서 쓰인 컬럼만 쓸 수 있음(P5-6)
• deptno로 그룹화 하고 그 그룹에서 ename를 출력하려고 한다면 그룹화된 사원중 어떤 사원의 이름을 출력해야하는지 모름, 그래서 오류~

1. // SELECT절에서 ename은 쓸수 없으므로 오류~
2. SQL> SELECT ename, deptno, MAX(sal)
     2  FROM emp
     3  GROUP BY deptno;
3. 
   SELECT ename, deptno, MAX(sal)
          *
   ERROR at line 1:
   ORA-00979: not a GROUP BY expression

 

 

HAVING Clause (P5-7)

• 그룹화 전에 select할 조건이랑 그룹화 후에 select할 조건을 구분해야함
• where절에는 그룹화전에 select할 조건을 쓰고, having절에는 그룹화 후에 selet할 조건을 입력한다.

 

• 부서번호 10, 20인 사원의 직종별 봉급 총합이 5000 이상인 직종출력, 직종별 평균급여 출력 (P5-8)

1. SQL> SELECT job, AVG(sal) PAYROLL
     2  FROM emp
     3  WHERE deptno IN(10, 20)
     4  GROUP BY job
     5  HAVING SUM(sal) > 5000
     6  ORDER BY AVG(sal);
2.  
3. JOB          PAYROLL
   --------- ----------
   MANAGER       2712.5
   ANALYST         3000

 

•  직종이 's'문자로 시작하는 사원들의 부서별 평균급여가 1000이상인 부서번호와 평균급여 출력

1. SQL>  SELECT deptno, AVG(sal) as 평균급여
     2   FROM emp
     3   WHERE job LIKE 'S%'
     4   GROUP BY deptno
     5   HAVING AVG(sal) >= 1000
     6   ORDER BY AVG(sal);
2.  
3.     DEPTNO   평균급여
   ---------- ----------
           30       1400
4.  
5. // JOIN문을 이용해서 부서이름 출력~
6. SQL> SELECT dept.deptno, avg(sal) as 평균급여, dept.dname 부서이름
     2  FROM emp, dept
     3  WHERE job like 'S%' AND emp.deptno = dept.deptno
     4  GROUP BY dept.deptno, dept.dname
     5  HAVING avg(sal) >= 1000;
7.  
8.     DEPTNO   평균급여 부서이름
   ---------- ---------- --------------
           30       1400 SALES 

이 글은 스프링노트에서 작성되었습니다.