[SQL] group 외 예제

[앞으로의 진도]

⊙ group by

⊙ having

⊙ 뷰

⊙ 인덱스

⊙ 테이블조인 (정규형)

⊙ 프로시저 → JDBC

 

[group by]

⊙ group by : 같은 데이터끼리 묶을 때, 집계함수와 많이 사용

⊙ 집계함수 : sum(), avg(), max(), min(), count

⊙ 예제

 문1) 부서코드별 급여정보 조회하기

select deptno from emp

group by deptno;

 

 

+) 오름차순 정렬

select deptno from emp

group by deptno order by deptno;

 

select deptno, ename
from emp
group by deptno
order by deptno;

에러 ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression"
▶ 요약기준 필드만 볼 수 있음, 대표성을 띄지 않는 (ename) 칼럼을 같이 쓸 경우 발생
ex) 그룹을 지어서 10이 5명일 경우 한 명만 대표로 뽑아 낼 수 없기 때문에 다른 칼럼이 들어가게 되면 에러발생

 

문2) deptno 부서코드별로 부서코드, 급여의 합, 급여 평균 (소수점 1자리), 최고 급여, 최저급여, 급여의 수를 조회하여 부서코드를 기준으로 정렬하시오

 

 

 

 

select deptno as 부서코드, sum(sal), round(avg(sal), 1), max(sal), min(sal), count(sal) from emp

group by deptno

order by 부서코드;

 

문3) 부서코드별 급여합계를 구하시오

select deptno as 부서코드, sum(sal)

from emp

group by deptno order by 부서코드;

 

[having조건절]

⊙ group by 에 사용하는 조건

⊙ 예제

문1) 부서코드별 급여합계를 구해서 그 합계 값이 1500이상만 조회하시오

select deptno as 부서코드, sum(sal) from emp

group by deptno having sum(sal)>=1500;

 

select deptno 부서코드, sum(sal) as 급여합계 --4)as 뒤에 쓰는 것에서 공백 주면 안됨
from emp                                              --1)
group by deptno                                     --2)
having 급여합계>=1500;                           --3)

에러 ORA-00904: "급여합계": invalid identifier 00904. 00000 - "%s: invalid identifier"
▶ 맞지 않는 칼럼명을 썼기 때문(deptno자리에 급여합계), 실행순서상 having 절이 as보다 먼저 수행되었기 때문

 

문2) 급여가 300이상 데이터 중에서 부서코드별 급여평균을 구해서 급여순으로 조회하시오

select deptno, avg(sal) from emp

where sal>=300

group by deptno order by avg(sal);

 

문3) 급여가 300이상 데이터 중에서 부서코드별 급여평균이 400이상을 급여순으로 조회하시오

select deptno, avg(sal) 급여평균 from emp

where sal>=300

                                    group by deptno

                                    having avg(sal)>=400

                                    order by 급여평균

 

문4)  부서별 급여총액(급여+커미션)이 가장 높은 급여총액과 가장 낮은 급여총액, 급여총액의 평균금액을 반올림해서 소수점 1자리까지만 출력

select deptno, max(sal+comm) 최고급여총액, min(sal+comm) 최저급여총액, round(avg(sal+comm), 1) 급여총액평균

from emp group by depton;

 

문5) hiredate 칼럼을 사용하여 월별로 입사한 인원수를 구하시오

① select to_char(hiredate,'mm') from emp;   → 문자열변환을 사용해서 입사일의 달만 뽑음

② select to_char(hiredate,'mm') count(*) from emp;

 

select to_char(hiredate,'mm') || '월' as 입사월, count(*)||'명' as 명수

from emp

group by hiredate order by hiredate;