SQL 03 그룹함수

각 부서와 업무 별로 가장 높은 급여를 출력하시오.

select deptno, job, max(sal) 
from emp
group by deptno, job;

급여가 가장 많은 부서번호를 출력하시오.

select deptno
from emp
where sal in (select max(sal)
              from emp);

각 부서 별로 몇 명이 업무를 하는지 출력하시오.

select deptno, job, count(*)
from emp
group by deptno, job;

커미션을 받는 직원 수를 출력하시오.

select count(comm)
from emp
where nvl(comm, 0) > 0;

커미션의 평균을 출력하시오.

select avg(comm)
from emp;

커미션의 총합을 출력하시오.

select sum(comm)
from emp;

커미션을 받는 직원 수를 출력하시오.

select count(comm) 
from emp;

직원들이 받는 커미션의 평균을 출력하시오. (잘못된 예시)

select sum(comm) / count(1)
from emp;

직원들이 받는 커미션의 평균을 출력하시오.

select avg(comm) from emp
where comm > 0;

일반컴럼을 그룹함수와 쓰려면 group by를 사용해야 한다.

where절에 그룹함수를 쓰면 안됨.

부서 별로 급여의 평균이 2000보다 큰 값을 출력하시오. (잘못된 예시)

select deptno, avg(sal)
from emp
where avg(sal) > 2000
group by deptno;

group by를 쓰고나서 having을 쓸 것.

부서 별로 급여의 평균이 2000보다 큰 값을 출력하시오.

select deptno, trunc(avg(sal))
from emp
group by deptno
having avg(sal) > 2000;

부서번호가 10이고 급여의 평균이 2000보다 큰 급여를 출력하시오.

select deptno, trunc(avg(sal))
from emp
where deptno = 10
group by deptno
having avg(sal) > 2000;

부서 별로 급여의 평균이 2000보다 큰 급여를 출력하시오. (부서번호 오름차순)

select deptno, trunc(avg(sal))
from emp
group by deptno
having avg(sal) > 2000
order by deptno;

부서 별로 급여의 평균이 2000보다 큰 급여를 출력하시오. (from에 select문 넣기)

select *
from (select deptno, trunc(avg(sal)) as avg_sal
      from emp
      group by deptno) tbl
where avg_sal >= 2000;

부서 별로 직원들의 수를 출력하시오. (select에 select문 넣기)

select (select count(1)
        from emp
        where deptno = 10) cnt10,
       (select count(1)
        from emp
        where deptno = 20) cnt20,
       (select count(1)
        from emp
        where deptno = 30) cnt30
from dual;

부서 별로 직원들의 수를 출력하시오. (select에 select문 넣기 + decode 이용)    

select sum(decode(deptno, 10, 1, 0)) cnt10,
       sum(decode(deptno, 20, 1, 0)) cnt20,
       sum(decode(deptno, 30, 1, 0)) cnt30
from emp;

최소 급여를 받는 사원과 같은 부서에서 근무하는 사원의 이름, 급여를 출력하시오.

select ename, sal
from emp
where deptno = (select deptno
                from emp
                where sal = (select min(sal) 
                             from emp));

최소 급여를 받는 사원과 같은 부서에서 근무하는 사원의 이름, 급여, 부서를 출력하시오.

select e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno 
      and d.deptno = (select deptno
                      from emp
                      where sal = (select min(sal) 
                                   from emp));

--1. EMP 테이블에서 10번부서 급여의 평균, 최고, 최저, 급여를 받는 인원 수 출력

--(조건 평균 급여가 많은 순으로 출력)

select avg(sal), max(sal), min(sal), count(sal)
from emp
where deptno = 10
order by avg(sal) desc;

--2. EMP 테이블에서 각 부서별 급여의 평균,최고,최저 출력(조건:부서 오름차순 정렬)

select deptno, avg(sal), max(sal), min(sal)
from emp
group by deptno
order by deptno;

--3. EMP 테이블에서 같은업무를 하는 사람의 수가 4명 이상인 업무와 인원수 출력

select job, count(1)
from emp
group by job
having count(1) >= 4;

--4. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 

--부서번호, 인원수, 급여의 합을 출력하시오

select deptno, count(1), sum(sal)
from emp
group by deptno
having count(1) >= 4;

--5. EMP 테이블에서 각 부서별 같은 업무를 하는 사람의 인원수를 구하여 

--부서번호,업무명,인원수출력(조건 부서번호 오름차순, 업무 내림차순 정렬)

select deptno, job, count(1) cnt
from emp
group by deptno, job
order by deptno, job desc;

--6. EMP 테이블에서 가장 많은 사원을 갖는 MGR번호 출력

select mgr as eno
from emp
group by mgr
having count(mgr) = (select max(count(1))
                     from emp
                     group by mgr);

--7. EMP 테이블에서 부서번호가 20인 부서의 이름, 급여, 시간당급여 출력 

--(조건:시간당급여 내림차순 정렬, 1달 근무일수:12일, 1일 근무시간:8시간)

select d.dname, e.sal, trunc((e.sal/12/8)) as sigueb
from emp e, dept d
where d.deptno = 20
order by sigueb desc;

--8. EMP 테이블에서 입사일이 90일이 지난 후의 

--사원명, 입사일, 90일 후 급여, 90일 후 급여일을 출력

select ename, hiredate, 3*sal, hiredate + 90 as "After 90 days"
from emp;

--9. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하시오.

select sum(decode(deptno, 10, 1, 0)) cnt10,
      sum(decode(deptno, 30, 1, 0)) cnt30
from emp;

select (select count(1)
        from emp
        where deptno = 10) cnt10,
       (select count(1)
        from emp
        where deptno = 30) cnt30
from dual;

--10. EMP 테이블에서 각 부서 별 입사일이 가장 오래된 사원을 

--한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하시오.

select empno, ename, deptno, hiredate
from emp
where (deptno, hiredate) in (select deptno, min(hiredate)
                             from emp
                             group by deptno)
order by deptno;

select e.empno, e.ename, e.deptno, e.hiredate
from emp e, (select deptno, min(hiredate) mdate
             from emp
             group by deptno) t
where e.deptno = t.deptno 
      and e.hiredate = t.mdate
order by deptno;

각 유저별 구매 상품 총 금액을 출력하시오. (총 금액이 높은 순으로)

select o.user_id, u.user_name, to_char(sum(o.price), '999,999,999') as sum
from orders o, users u
where o.user_id = u.user_id
group by o.user_id, u.user_name
order by sum desc;

각 유저 별로 1월에 구매 상품 총 금액을 출력하시오. 

select user_id, sum(price) sum
from orders
where to_char(rdate, 'MM') = 01
group by user_id;

각 유저 별로 구매 상품 총 금액이 20000보다 큰 값을 출력하시오.

(조건 - 총 금액이 높은 순으로)

select user_id, sum(price) sum
from orders
group by user_id
having sum > 20000;

유저별로 주문상품, 구매금액, 구매수량을 출력하시오.

조건: 유저별 오름차순, 주문일 오름차순

select user_id, good_code, nvl(sum(price), 0), nvl(sum(amount), 0)
from orders
group by user_id, good_code
order by user_id;  

  

select t.user_id, o.good_code, nvl(sum(o.price), 0), nvl(sum(o.amount), 0)
from orders o, (select distinct user_id
                from users) t
where o.user_id(+) = t.user_id
group by t.user_id, o.good_code
order by t.user_id;

월별 구매 금액을 출력하시오.

select 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 01) JAN,
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 02) FEB,
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 03) MAR, 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 04) APR, 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 05) MAY, 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 06) JUN, 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 07) JUL, 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 08) AUG, 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 09) SEP, 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 10) OCT, 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 11) NOV, 
(select nvl(sum(price), 0)
from orders
where to_char(rdate, 'MM') = 12) DEC 
from dual;

select t.mon as MON, nvl(sum(o.price), 0) as sum
from orders o, temp_mon t
where to_char(o.rdate(+), 'MM') = t.mon
group by t.mon
order by t.mon;

select 
    sum(decode(to_char(rdate, 'MM'), '01', price, 0)) jan,
    sum(decode(to_char(rdate, 'MM'), '02', price, 0)) feb,
    sum(decode(to_char(rdate, 'MM'), '03', price, 0)) mar,
    sum(decode(to_char(rdate, 'MM'), '04', price, 0)) apr,
    sum(decode(to_char(rdate, 'MM'), '05', price, 0)) may,
    sum(decode(to_char(rdate, 'MM'), '06', price, 0)) jun,
    sum(decode(to_char(rdate, 'MM'), '07', price, 0)) jul,
    sum(decode(to_char(rdate, 'MM'), '08', price, 0)) aug,
    sum(decode(to_char(rdate, 'MM'), '09', price, 0)) sep,
    sum(decode(to_char(rdate, 'MM'), '10', price, 0)) oct,
    sum(decode(to_char(rdate, 'MM'), '11', price, 0)) nov,
    sum(decode(to_char(rdate, 'MM'), '12', price, 0)) dec
from orders;

유저 별 월 별 구매 금액, 구매횟수 통계를 출력하시오.

select 
    user_id,
    sum(decode(to_char(rdate, 'MM'), '01', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '01', amount, 0)) jan,
    sum(decode(to_char(rdate, 'MM'), '02', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '02', amount, 0)) feb,
    sum(decode(to_char(rdate, 'MM'), '03', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '03', amount, 0)) mar,
    sum(decode(to_char(rdate, 'MM'), '04', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '04', amount, 0)) apr,
    sum(decode(to_char(rdate, 'MM'), '05', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '05', amount, 0)) may,
    sum(decode(to_char(rdate, 'MM'), '06', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '06', amount, 0)) jun,
    sum(decode(to_char(rdate, 'MM'), '07', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '07', amount, 0)) jul,
    sum(decode(to_char(rdate, 'MM'), '08', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '08', amount, 0)) aug,
    sum(decode(to_char(rdate, 'MM'), '09', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '09', amount, 0)) sep,
    sum(decode(to_char(rdate, 'MM'), '10', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '10', amount, 0)) oct,
    sum(decode(to_char(rdate, 'MM'), '11', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '11', amount, 0)) nov,
    sum(decode(to_char(rdate, 'MM'), '12', price, 0))||'/'||
      sum(decode(to_char(rdate, 'MM'), '12', amount, 0)) dec
from orders
group by user_id;

가장 많이 구매한 유저, 구매금액은? -- vvip 출력

select user_id as vvip, sum(price) sum
from orders
group by user_id
having sum(price) = (select max(sum(price))
                     from orders
                     group by user_id);