SQL 03 그룹함수

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

1
2
3
select deptno, job, max(sal) 
from emp
group by deptno, job;
cs

급여가 가장 많은 부서번호를 출력하시오.
1
2
3
4
select deptno
from emp
where sal in (select max(sal)
              from emp);
cs

각 부서 별로 몇 명이 업무를 하는지 출력하시오.
1
2
3
select deptno, job, count(*)
from emp
group by deptno, job;
cs

커미션을 받는 직원 수를 출력하시오.
1
2
3
select count(comm)
from emp
where nvl(comm, 0> 0;
cs

커미션의 평균을 출력하시오.
1
2
select avg(comm)
from emp;
cs

커미션의 총합을 출력하시오.
1
2
select sum(comm)
from emp;
cs

커미션을 받는 직원 수를 출력하시오.
1
2
select count(comm) 
from emp;
cs

직원들이 받는 커미션의 평균을 출력하시오. (잘못된 예시)
1
2
select sum(comm) / count(1)
from emp;
cs

직원들이 받는 커미션의 평균을 출력하시오.
1
2
select avg(comm) from emp
where comm > 0;
cs

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

부서 별로 급여의 평균이 2000보다 큰 값을 출력하시오. (잘못된 예시)
1
2
3
4
select deptno, avg(sal)
from emp
where avg(sal) > 2000
group by deptno;
cs

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

부서 별로 급여의 평균이 2000보다 큰 값을 출력하시오.
1
2
3
4
select deptno, trunc(avg(sal))
from emp
group by deptno
having avg(sal) > 2000;
cs

부서번호가 10이고 급여의 평균이 2000보다 큰 급여를 출력하시오.
1
2
3
4
5
select deptno, trunc(avg(sal))
from emp
where deptno = 10
group by deptno
having avg(sal) > 2000;
cs

부서 별로 급여의 평균이 2000보다 큰 급여를 출력하시오. (부서번호 오름차순)
1
2
3
4
5
select deptno, trunc(avg(sal))
from emp
group by deptno
having avg(sal) > 2000
order by deptno;
cs

부서 별로 급여의 평균이 2000보다 큰 급여를 출력하시오. (from에 select문 넣기)
1
2
3
4
5
select *
from (select deptno, trunc(avg(sal)) as avg_sal
      from emp
      group by deptno) tbl
where avg_sal >= 2000;
cs

부서 별로 직원들의 수를 출력하시오. (select에 select문 넣기)
1
2
3
4
5
6
7
8
9
10
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;
cs

부서 별로 직원들의 수를 출력하시오. (select에 select문 넣기 + decode 이용)    
1
2
3
4
select sum(decode(deptno, 1010)) cnt10,
       sum(decode(deptno, 2010)) cnt20,
       sum(decode(deptno, 3010)) cnt30
from emp;
cs

최소 급여를 받는 사원과 같은 부서에서 근무하는 사원의 이름, 급여를 출력하시오.
1
2
3
4
5
6
select ename, sal
from emp
where deptno = (select deptno
                from emp
                where sal = (select min(sal) 
                             from emp));
cs

최소 급여를 받는 사원과 같은 부서에서 근무하는 사원의 이름, 급여, 부서를 출력하시오.
1
2
3
4
5
6
7
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));
cs


--1. EMP 테이블에서 10번부서 급여의 평균, 최고, 최저, 급여를 받는 인원 수 출력
--(조건 평균 급여가 많은 순으로 출력)
1
2
3
4
select avg(sal), max(sal), min(sal), count(sal)
from emp
where deptno = 10
order by avg(sal) desc;
cs

--2. EMP 테이블에서 각 부서별 급여의 평균,최고,최저 출력(조건:부서 오름차순 정렬)
1
2
3
4
select deptno, avg(sal), max(sal), min(sal)
from emp
group by deptno
order by deptno;
cs

--3. EMP 테이블에서 같은업무를 하는 사람의 수가 4명 이상인 업무와 인원수 출력
1
2
3
4
select job, count(1)
from emp
group by job
having count(1>= 4;
cs

--4. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 
--부서번호, 인원수, 급여의 합을 출력하시오
1
2
3
4
select deptno, count(1), sum(sal)
from emp
group by deptno
having count(1>= 4;
cs

--5. EMP 테이블에서 각 부서별 같은 업무를 하는 사람의 인원수를 구하여 
--부서번호,업무명,인원수출력(조건 부서번호 오름차순, 업무 내림차순 정렬)
1
2
3
4
select deptno, job, count(1) cnt
from emp
group by deptno, job
order by deptno, job desc;
cs

--6. EMP 테이블에서 가장 많은 사원을 갖는 MGR번호 출력
1
2
3
4
5
6
select mgr as eno
from emp
group by mgr
having count(mgr) = (select max(count(1))
                     from emp
                     group by mgr);
cs

--7. EMP 테이블에서 부서번호가 20인 부서의 이름, 급여, 시간당급여 출력 
--(조건:시간당급여 내림차순 정렬, 1달 근무일수:12일, 1일 근무시간:8시간)
1
2
3
4
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;
cs

--8. EMP 테이블에서 입사일이 90일이 지난 후의 
--사원명, 입사일, 90일 후 급여, 90일 후 급여일을 출력
1
2
select ename, hiredate, 3*sal, hiredate + 90 as "After 90 days"
from emp;
cs

--9. EMP 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력하시오.
1
2
3
select sum(decode(deptno, 1010)) cnt10,
      sum(decode(deptno, 3010)) cnt30
from emp;
cs

1
2
3
4
5
6
7
select (select count(1)
        from emp
        where deptno = 10) cnt10,
       (select count(1)
        from emp
        where deptno = 30) cnt30
from dual;
cs

--10. EMP 테이블에서 각 부서 별 입사일이 가장 오래된 사원을 
--한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하시오.
1
2
3
4
5
6
select empno, ename, deptno, hiredate
from emp
where (deptno, hiredate) in (select deptno, min(hiredate)
                             from emp
                             group by deptno)
order by deptno;
cs

1
2
3
4
5
6
7
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;
cs

각 유저별 구매 상품 총 금액을 출력하시오. (총 금액이 높은 순으로)
1
2
3
4
5
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;
cs

각 유저 별로 1월에 구매 상품 총 금액을 출력하시오. 
1
2
3
4
select user_id, sum(price) sum
from orders
where to_char(rdate, 'MM'= 01
group by user_id;
cs

각 유저 별로 구매 상품 총 금액이 20000보다 큰 값을 출력하시오.
(조건 - 총 금액이 높은 순으로)
1
2
3
4
select user_id, sum(price) sum
from orders
group by user_id
having sum > 20000;
cs

유저별로 주문상품, 구매금액, 구매수량을 출력하시오.
조건: 유저별 오름차순, 주문일 오름차순
1
2
3
4
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;  
cs
  
1
2
3
4
5
6
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;
cs

월별 구매 금액을 출력하시오.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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;
cs

1
2
3
4
5
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;
cs


1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;
cs

유저 별 월 별 구매 금액, 구매횟수 통계를 출력하시오.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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;
cs

가장 많이 구매한 유저, 구매금액은? -- vvip 출력
1
2
3
4
5
6
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);
cs




'개 발 :: development > 질의문 :: sql' 카테고리의 다른 글

SQL 04 테이블/시퀀스/인덱스  (0) 2017.12.27
SQL 03 그룹함수  (0) 2017.12.27
SQL 02 조건문/조인/그룹함수  (0) 2017.12.27
SQL 01 기초  (0) 2017.12.27

티스토리 툴바