SQL 함수(그룹 함수)

1. count : 행의 개수 출력
가. count(*) : null의 개수도 포함, 중복된 값도 포함(중복된 값 제외시 distinct 사용)
나. count(col_name) : null 제외, 중복된 값도 포함(중복된 값 제외시 distinct 사용)

2. max : null 을 제외한 모든행에서 최대값 출력

3. min : null 을 제외한 모든행에서 최소값 출력

4. sum : null 을 제외한 모든 행의 합게

5. avg : null 을 제외한 행의 평균값
null을 포함한 row 값도 평균에 반영하고자 할 때에는 nvl(col_name, 0) 함수를 이용한다.

6. stddev : null 을 제외한 모든 행의 표준편차

7. variance : null 을 제외한 모든 행의 분산

8. grouping : 해당 컬럼이 그룹에 사용되었는지 여부를 1 또는 0으로 반환

9. grouping set : 한번의 질이로 여러개의 그룹화 가능

10. group by 절의 사용규칙
가. 그룹핑 전에 where 절을 사용하여 그룹 대상 집합 선정가능
나. select 절에 나열된 컬럼이나, 표현식은 group by 절에 명시해야 함
다. group by 절에 나열된 컬럼은 select 절에 명시 하지 않아도 됨
라. group by 절에는 select 절에 사용한 alias 를 사용할 수 없음


11, rollup : group by 절의 그룹조건에 따라 전체 행을 그룹화하고 각 그룹에 대한 부분합을 구하는 연산잔

12. cube: rollup의 그룹 결과와 group by 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자

참고 : group by 절에 기술된 칼럼 수가 n개 인경우
rollup : n+1
cube : 2^n

연습문제 :
1 .학과별로 평균 몸무게와 학생수를 출력하되 평균 몸무게의 내림차순으로 정렬하세요.

SQL> select deptno, avg(nvl(weight, 0)), count(*)
  2  from student
  3  group by deptno
  4  order by 2 desc
  5  ;

    DEPTNO AVG(NVL(WEIGHT,0))   COUNT(*)
---------- ------------------ ----------
       102               69.5          4
       101                 68          8
       201              61.75          4

2. 동일 학과 내에서 같은 학년에 재학중인 학생 수가 3명 이상인 그룹의 학과번호, 학년, 학생 수, 최대 키, 최대 몸무게를 출력하세요.

SQL> select deptno, grade, count(*), max(height), max(weight)
  2  from student
  3  group by deptno, grade
  4  having count(deptno) >= 3 and count(grade) >=3;

    DEPTNO G   COUNT(*) MAX(HEIGHT) MAX(WEIGHT)
---------- - ---------- ----------- -----------
       201 1          3         184          70
       101 2          3         171          72

3. 학과와 학년 별 키의 평균, 학과별 키의 평균, 전체 학생에 대한 키의 평균을 구하세요.

SQL> select deptno, grade, avg(nvl(height,0))
  2   from student
  3  group by rollup(deptno, grade);

    DEPTNO G AVG(NVL(HEIGHT,0))
---------- - ------------------
       101 1                177
       101 2         164.666667
       101 3                170
       101 4              175.5
       101              171.125
       102 1                160
       102 2                164
       102 3                171
       102 4                177
       102                  168
       201 1         179.333333
       201 2                166
       201                  176
                       171.5625

14 rows selected.

4. 학과별 평균 몸무게와 학년 별 평균 몸무게를 구하세요

SQL> select deptno, grade, round(avg(nvl(weight,0)))
  2  from student
  3* group by grouping sets((deptno), (grade))

    DEPTNO G ROUND(AVG(NVL(WEIGHT,0)))
---------- - -------------------------
       201                          62
       102                          70
       101                          68
           1                        65
           3                        79
           4                        85
           2                        53
7 rows selected.
 

5. 학과별 교수 수가 2명 이하인 학과 번호, 교수 수를 출력 하세요.

SQL> select deptno, count(position)
  2  from professor
  3  where position='교수'
  4  group by deptno
  5  having count(deptno) <= 2;

    DEPTNO COUNT(POSITION)
---------- ---------------
       102               1
       101               1

6. 교수 중에서 급여와 보직수당을 합친 금액이 가장 많은 경우와 가장 적은 경우 그리고 평균 금액을 출력하여라. 단, 보직수당이 없는 교수의 수당은 0으로 계산하고, 급여는 소수점 둘째 자리에서 반올림하세요.

SQL> select round(avg(nvl(sal,0)+nvl(comm,0)),1) sal_avg,
  2         round(max(sal+nvl(comm,0)),1) sal_max,
  3         round(min(sal+nvl(comm,0)),1) sal_min
  4  from professor;

   SAL_AVG    SAL_MAX    SAL_MIN
---------- ---------- ----------
     372.1        520        210

7. 학과와 직급별 급여 평균, 학과별 급여 평균, 직급별 급여 평균 그리고 교수 전체에 대한 급여 평균을 출력하세요.

SQL> select deptno, position, avg(nvl(sal,0)) sal
  2  from professor
  3  group by cube(deptno, position);

    DEPTNO POSITION                    SAL
---------- -------------------- ----------
                                     362.5
           교수                        475
           부교수                      410
           조교수                      340
           전임강사                    225
       101                           372.5
       101 교수                        500
       101 부교수                      420
       101 조교수                      360
       101 전임강사                    210
       102                             345
       102 교수                        450
       102 전임강사                    240
       201                             320
       201 조교수                      320
       202                             400
       202 부교수                      400

17 rows selected.

8. 직급별로 평균 급여가 300보다 크면 ‘우수’, 작거나 같으면 ‘보통’을 출력하세요.

SQL> select position, avg(nvl(sal, 0)) sal,
  2     case when avg(nvl(sal,0)) > 300 then '우수'
  3               else '보통'
  4     end mark
  5  from professor
  6  group by position;

POSITION                    SAL MARK
-------------------- ---------- ----
교수                        475 우수
부교수                      410 우수
조교수                      340 우수
전임강사                    225 보통

9. Emp 테이블의 hiredate 컬럼을 참고해서 아래 결과처럼 출력해보세요.

SQL> select count(*) hap,
  2      count(decode(to_char(hiredate,'rrrr'),'1980',1)) "1980",
  3      count(decode(to_char(hiredate,'rrrr'),'1981',1)) "1981",
  4      count(decode(to_char(hiredate,'rrrr'),'1982',1)) "1982",
  5      count(decode(to_char(hiredate,'rrrr'),'1987',1)) "1987"
  6  from emp;

       HAP       1980       1981       1982       1987
---------- ---------- ---------- ---------- ----------
        14          1         10          1          2

10. Emp table을 사용하여 부서별로 직급별로 급여 합계를 구하고 합계를 구하세요.

SQL> select deptno,
  2      sum(decode(job,'CLERK',sal,0)) "CLERK",
  3      sum(decode(job,'MANAGER',sal,0)) "MANAGER",
  4      sum(decode(job,'PRESIDENT',sal,0)) "PRESIDENT",
  5      sum(decode(job,'ANALYST',sal,0)) "ANALYST",
  6      sum(decode(job,'SALESMAN',sal,0)) "SALESMAN",
  7      sum(sal) "HAP"
  8  from emp
  9  where job is not null
 10  group by rollup(deptno)
 11  order by deptno;

    DEPTNO      CLERK    MANAGER  PRESIDENT    ANALYST   SALESMAN        HAP
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10       1300       2450       5000          0          0       8750
        20       1900       2975          0       6000          0      10875
        30        950       2850          0          0       5600       9400
                 4150       8275       5000       6000       5600      29025

11. temp 테이블을 사용하여 아래와 같이 출력하시오

SQL> select ceil(rownum/3) no,
  2     max(decode(mod(rownum,3), 1, emp_id)) "사번1",
  3     max(decode(mod(rownum,3), 1, emp_name)) "이름1",
  4     max(decode(mod(rownum,3), 2, emp_id)) "사번2",
  5     max(decode(mod(rownum,3), 2, emp_name)) "이름2",
  6     max(decode(mod(rownum,3), 0, emp_id)) "사번3",
  7     max(decode(mod(rownum,3), 0, emp_name)) "이름3"
  8  from temp
  9  group by ceil(rownum/3)
 10* order by 1


        NO      사번1 이름1           사번2 이름2           사번3 이름3
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1   19970101 김길동       19960101 홍길동       19970201 박문수
         2   19930331 정도령       19950303 이순신       19966102 지문덕
         3   19930402 강감찬       19960303 설까치       19970112 연흥부
         4   19960212 배뱅이       20000101 이태백       20000102 김설악
         5   20000203 최오대       20000334 박지리       20000305 정북악
         6   20006106 유도봉       20000407 윤주왕       20000308 강월악
         7   20000119 장금강       20000210 나한라

7 rows selected.