SUBGROUP으로 데이터 분류

GROUP BY, HAVING절

SELECT : 칼럼명, GROUP 함수
FROM : 테이블명
WHERE : 조건식
GROUP BY : 칼럼명
HAVING : 조건식
ORDER BY : 칼럼이나 표현식

• GROUP BY : 전체 데이터를 소그룹으로 나눌 칼럼을 명시한다.

SELECT dept_id, AVG(salary)
FROM s_emp
WHERE title = '사원'
GROUP BY dept_id;

예제)

--Q5.각 부서별(s_emp.dept_id) 평균 급여(avg(salary)를 계산해서 보여주시오.
select dept_id, avg(salary) from s_emp  --dept를 하나의 그룹으로 묶어서 급여를 계산한것.
group by dept_id
;

--Q6.각 부서별(dept_id)로 직책(title)이 사원인 직원들의 평균(avg(salary)) 급여 계산해서 보여주시오
select dept_id, sum(salary), trunc(avg(salary)) from s_emp
where title = '사원'
group by dept_id
;
 
select dept_id, count(*), sum(salary), avg(salary) from s_emp--값 체크 비교위해 생성
where dept_id = 113
group by dept_id
order by dept_id
;

 

※ SELECT절에서 GROUP함수의 잘못된 사용

SELECT rgion_id, COUNT(name)
FROM s_dept;
SELECT region_id, COUNT(name)
*
ERROR at line 1:
ORA - 00937 : not a gingle - group group function

SELECT절에 그룹함수가 오거나, GROUP BY절 이하에 기술된 칼럼이 오면 나머지 칼럼은 SELECT절 뒤에 기술할 수 없다.

예제)

select dept_id, count(*) from s_emp--select절에 group함수와 같이 오려면 group by가 필수
group by dept_id
order by dept_id
;

--Q7.각 지역(region_id)별로 몇개의 부서가 잇는지를 나타내시오.
select region_id, count(*) from s_dept  --s_dept에 있는 region_id를 그룹별로 묶은다음 행의 갯수를 카운트한다.
group by region_id
order by region_id
;

※WHERE절에서 GROUP함수의 잘못된 사용

SELECT dept_id, AVG(salary)
FROM s_emp
WHERE AVG(salary) >=2000
GROUP BY dept_id;
WHERE AVG(salary) >= 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here

GROUP에 대한 조건은 WHERE절에서 기술할 수 없고, HAVING절에서 기술하여야 한다.

• HAVING : GROUP에 대한 조건을 기술한다.

SELECT title, AVG(salary), COUNT(*)
FROM s_emp
GROUP BY title
HAVING COUNT(*) >2 ;

직책에 대해 묶여있는 특정 직급이 2초과

예제)

--Q9. 각 직책별로 급여의 총합을 구하되 직책이 부장인 사람은 제외하시오.
--단, 급여총합이 8000(만원) 이상인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬하시오.
select title, sum(salary) from s_emp --직책별로 합을 구한다.
where title not like '%부장'--부장이 들어간거 제외시킨다. 와일드카드는 like에서만 사용가능 다른곳에서는 와일드카드로 인식하지 않는다.
group by title --타이틀별로 그룹을 만들어준다.
having trunc(sum(salary)) >=8000 --그룹별로 조건식을 지정해준다.
order by 2 asc--sum(salary) 두번째 컬럼인경우 숫자만 사용해서도 가능하다.
;

--Q10. 각 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오.
select dept_id, trunc(avg(salary)) from s_emp --직책별로 평균을 구하고
where title = '사원'
group by dept_id --그룹을 만들어준다.
;

 

GROUP을 SubGroup으로 세분화하기

SELECT dept_id, title, Count(*)
FROM s_emp
GROUP BY dept_id, title;

Group By절 이하에 Group으로 나누고자 하는 칼럼을 여러개 기술함으로써 그룹을 세분화 한다.

예제)

--Q1. 각 부서(dept_id)내에서 각 직책(title)별로 몇명의 인원(count)이 있는지를 나타내시오.(subgroup)
Select dept_id, title, count(*)
from s_emp
group by dept_id, title
order by dept_id
;

 

--Q2.각 부서(dept_id)내에 몇명의 직원(count)이 근무하는 지를 나타내시오 (subgroup)
SELECT dept_id, count(*)
from s_emp
group by dept_id
order by dept_id
;

 

--Q3.각 부서별(dept_id)로 급여의 최소값(min(salary)과 최대값(max(salary)을 나타내시오. 단, 최소값고 최대값이 같은 부서는 출력하지 마시오.
SELECT dept_id, max(salary),min(salary)
FROM s_emp
GROUP BY dept_id
HAVING min(salary) <> max(salary)  --그룹에 대한 조건식을 줘야한다..
order by 1
;