SQL group by having

 

--부서별 평균 급여
select deptno, avg(sal)
from emp
group by deptno;
 
--부서별 최대급여, 최소급여
select deptno, max(sal), min(sal)
from emp
group by deptno;
 
--사원 수와 커미션을 받는 사원들의 수를 계산
select deptno, count(*),count(comm)
from emp
group by deptno;
 
--그룹핑해서 deptno가 20인 자료만 선택하여 출력
select deptno, avg(sal) from emp
where deptno = 20
group by deptno;
 
--조건을 잡아서 deptno가 20인지 각각 물어보고, 해당 자료만 출력하는것.
select deptno, avg(sal) from emp
group by deptno
having deptno=20;
 
--2000이 넘는 자료들'만' 평균 계산
select deptno,avg(sal) from emp
where sal >= 2000
group by deptno;
 
--먼저 부서별로 평균계산을해서, 부서별 평균이 2000이상인 자료 출력
select deptno, avg(sal) from emp
group by deptno
having avg(sal)>=2000;
 
select deptno, max(sal), min(sal)
from emp
group by deptno
having max(sal)>=2900;
 
--부서별 연도별 평균 급여
select deptno, to_char(hiredate, 'yyyy'), avg(sal) from emp
group by deptno, to_char(hiredate, 'yyyy')
order by deptno, to_char(hiredate, 'yyyy');
 
--analyst , manager 는 그대로 나머지는 others
select job, decode(job, 'ANALYST', 'ANALYST', 'MANAGER', 'MANAGER', 'Others') as oth, sal
from emp;
 
--아날리스트는 아날리스트로, 매니저는 매니저로, 나머지는 others로 표기
select avg(sal)
from emp
group by decode(job, 'ANALYST', 'ANALYST', 'MANAGER', 'MANAGER', 'Others');
 
 
--부서별 평균 연봉
select deptno, avg(sal)
from emp
group by deptno;
 
--row를  column으로 표현 /dept10 dept20 dept30
select count(deptno), 
count(decode(deptno, 10, deptno)) as dept10,
count(decode(deptno, 20, deptno)) as dept20,
count(decode(deptno, 30, deptno)) as dept30
from emp;
 
--부서별 평균 급여를 출력하시오 
select deptno, avg(sal)
from emp
group by deptno;
--group by 안쓰고 표현 (가로로)
select decode(deptno, 10, sal) as d10,
decode(deptno, 20, sal) as d20,
decode(deptno, 30, sal) as d30
from emp;
select  round(avg(sal)), 
        round(avg(decode(deptno, 10, sal))) as dept10,
        round(avg(decode(deptno, 20, sal))) as dept20,    
        round(avg(decode(deptno, 30, sal))) as dept30
from emp;
 
 
--연도별 평균 급여를 출력하시오
select  round(avg(sal)), 
        round(avg(decode(to_char(hiredate,'yyyy'), 1980, sal))) as "1980",
        round(avg(decode(to_char(hiredate,'yyyy'), 1981, sal))) as "1981",    
        round(avg(decode(to_char(hiredate,'yyyy'), 1982, sal))) as "1982"
from emp
group by deptno;
 
--부서별 입사년도별 인원
select deptno, count(*), 
count(decode(to_char(hiredate,'yyyy'), '1980', 1)) as "1980",
count(decode(to_char(hiredate,'yyyy'), '1981', 1)) as "1981",
count(decode(to_char(hiredate,'yyyy'), '1982', 3)) as "1982"
from emp
group by deptno;