loading

그룹함수 - group by, having

2014. 5. 11. 15:14
728x90
반응형

ㅇ Group by


결과값에 대해서 지정한 컬럼을 기준으로 그룹화하여 출력


SQL> select deptno, sum(sal) 

    from emp

    group by deptno;


    DEPTNO   SUM(SAL)

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

30  9400

20 10875

10  8750



select절에 사용된 칼럼(그룹함수 제외)은 group by절에 사용되어야 함


SQL> select deptno, job, sum(sal)

from EMP

group by deptno;



select deptno, job, sum(sal)

                *

ERROR at line 1:

ORA-00979: not a GROUP BY expression




SQL> select deptno, job, sum(sal)

from EMP

group by deptno, job;


    DEPTNO JOB        SUM(SAL)

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

20 CLERK    1900

30 SALESMAN    5600

20 MANAGER    2975

30 CLERK     950

10 PRESIDENT    5000

30 MANAGER    2850

10 CLERK    1300

10 MANAGER    2450

20 ANALYST    6000


9 rows selected.




ㅇ Having


where절에는 그룹함수를 사용할 수 없으므로 대신 having절 사용

having절은 group by절 다음에 와야.


SQL> select deptno, avg(sal)

  2  from emp

  3  where avg(sal) > 1000

  4  group by deptno;

where avg(sal) > 1000

      *

ERROR at line 3:

ORA-00934: group function is not allowed here



SQL> select deptno, avg(sal)

  2  from emp

  3  group by deptno

  4  having avg(sal) > 1000;


    DEPTNO   AVG(SAL)

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

30 1566.66667

20  2175

10 2916.66667





728x90

또루아빠 SQL(오라클,mysql)