[SQL]집합함수(SUM,MAX,MIN,AVG),그룹화 함수

/*집합 함수, 그룹화 함수 */

select * from emp;
select ename,sal from emp;
select sum(sal) from emp;
rem select ename,sum(sal) from emp; 은 오류가 남 단일이랑 그룹은 함께 못씀 같이 그룹으로 만들어야 쓸 수 있음->GROUP BY

select sum(distinct sal),sum(all sal), sum(sal) from emp;

 

/*
SUM: 데이터의 합
COUNT: 데이터의 개수
MAX : 데이처의 최대값 반환
MIN : 데이터의 최소값 반환
AVG : 데이터의 평균값반환*/

 

SELECT MAX(SAL) FROM EMP;
SELECT MIN(SAL) FROM EMP;
SELECT ROUND(AVG(SAL),2) FROM EMP;

 

/*GROUP BY:단일+그룹*/
SELECT DEPTNO,JOB,ROUND(AVG(SAL),3)
FROM EMP
GROUP BY DEPTNO,JOB ;

select comm from emp;
SELECT count(comm) from emp where comm is not null;

/*부서 번호가 10번인 사원들의 최대 급여 구하기*/
select max(sal) from emp where deptno=10;

 

/*실습 7-16*/
select round(avg(sal),2), '10' as deptno from emp where deptno=10
union all
select round(avg(sal),2), '20' as deptno from emp where deptno=20
union all
select round(avg(sal),2), '30' as deptno from emp where deptno=30;

SELECT ROUND(AVG(SAL),2),DEPTNO FROM EMP GROUP BY DEPTNO;

 

/*GROUP BY에 조건을 주는 HAVING 절*/

SELECT DEPTNO,JOB,ROUND(AVG(SAL),3)
FROM EMP
GROUP BY DEPTNO,JOB
HAVING AVG(SAL)>=2000
ORDER BY DEPTNO,JOB;

SELECT ENAME,DEPTNO,ROUND(AVG(SAL),2)
FROM EMP
GROUP BY DEPTNO,ENAME;

 

/*P212 1번*/
SELECT DEPTNO,ROUND(AVG(SAL),0) AS AVG_SAL,MAX(SAL) AS MAX_SAL, MIN(SAL) AS MIN,COUNT(ENAME) AS CNT
FROM EMP
GROUP BY DEPTNO;

/*2번*/
SELECT JOB,COUNT(JOB)
FROM EMP
GROUP BY JOB
HAVING COUNT(JOB)>=3;

/*3번*/
SELECT SUBSTR(TO_CHAR(HIREDATE,'YYYY'),3,4),DEPTNO,COUNT (TO_CHAR(HIREDATE,'YYYY'))
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'YYYY'),DEPTNO
ORDER BY TO_CHAR(HIREDATE,'YYYY'),DEPTNO;