DataBase/oracle
oracle group by , having , rollup
HUCHUCHU
2020. 3. 15. 21:45

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
/* group by 절 */
-- distinct로 중복제거
select distinct department_id from employees;
-- group by로 묶어서 출력
select department_id from employees group by department_id;
-- 부서별 급여 합계
select department_id, sum(salary) from employees group by department_id;
select distnict department_id, sum(salary) from employees; -- 에러
-- 부서별 급여 합계 인원수 평균
select department_id , sum(salary), count(salary), trunc(avg(salary),2) from employees group by department_id;
-- 부서별, 직급별 사원수와 평균 급여
select department_id , job_id,sum(salary), count(salary), trunc(avg(salary),2) from employees group by department_id, job_id;
-- order by 추가
select department_id , job_id,sum(salary), count(salary), trunc(avg(salary),2)
from employees
group by department_id, job_id
order by department_id, job_id;
select department_id, job_id,
to_char(sum(salary), '999,999') 총급여, -- 형식을 지정할 수 있다!!!
to_char(avg(salary), '999,999') 평균급여
from employees
where department_id = 80
GROUP by department_id, job_id
order by department_id, job_id;
/* HAVING() 절 */
-- 부서별 사원수
select department_id, count(*) from employees
where department_id is not null
GROUP by department_id;
-- 사원수가 10명 이상인 부서
--(1) 에러
select department_id, count(*) from employees
where department_id is not null
and count(*)>10 -- where 절에서는 집계함수를 사용할 수 없다
GROUP by department_id;
--(2) having 사용 (보통은 group by 절 뒤에 사용하지만 앞에와서 상관은없다)
select department_id, count(*) from employees
where department_id is not null
GROUP by department_id
having count(*) >= 10 ;
/* rollup() 그룹별 합계 정보를 추가해서 보여줌 */
SELECT l.city, d.department_name, e.job_id,
count(*) 사원수, sum(e.salary) 총급여
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
group by ROLLUP( l.city, d.department_name, e.job_id) -- 부서별 합계(인원수/급여)가 출력된다
order by l.city, d.department_name, e.job_id;
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5text-decoration:none">Colored by Color Scripter
|
** rollup 적용 전

** rollup 적용 후 ( 도시별/부서별/직급을 출력하고 도시별/부서별의 사원수 합계 총 급여 합계를 보여준다)
