[Oracle] SQL 복수행 함수(그룹함수) - having, group by

• Having : 그룹 함수의 조건문

---

그룹 함수에서는 where 구문을 사용 할 수 없음. 이때 대신 사용하는 구분이 having 구문이다.

---

오류

•    ORA-00934 : 그룹 함수는 허가되지 않습니다.

 

---

예시 : 학년별 학생 중 가장 큰 학생의 키와 가장 작은 학생의 키, 평균키를 조회하기.
 평균키가 170이상인 학년만 조회하기.
select grade, max(height),min(height),avg(height) 
from student group by grade having avg(height)>= 170 order by 1;

---

문제 : 
 1. 학생테이블에서 전화번호의 지역번호 기준으로 학생 수 출력하기.
   학생 수가 3명 이상인 지역 조회하기.
select substr(tel,1,instr(tel,')')-1) 지역번호,count(*)학생수 
from student group by substr(tel,1,instr(tel,')')-1) having count(*) > 3;

select substr(tel,1,instr(tel,')')-1) 지역번호,count(*)학생수 
from student where count(*)>3                 ----(X) where 구문에서는 count 사용불가.
group by substr(tel,1,instr(tel,')')-1)

• group by : 컬럼의 값을 기준으로 레코드를 그룹화.

---

💡

주의 사항

1. select 절에 사용된 그룹함수 이외의 칼럼이나 표현식은 반드시 group by 절에 사용되어야 한다. 그렇지 않을 경우 에러가 발생.

2. group by 절에 사용된 칼럼은 SELECT 절에 사용되지 않아도 된다.

3. group by 절에는 반드시 칼럼 명이 사용되어야 하며 칼럼 Alias(별명) 는 사용 할 수 없다.

---

오류

• ORA-00979 : group by 표현식이 아닙니다.
• ORA-00904 : "DNO": 부적합한 식별자

---

예시 : 
 1. 학년별 학생의 인원수 조회하기.
select grade,count(*) from student group by grade order by 1;

예시 : 
 2. 학과1(deptno1)별 학생의 인원수를 조회하기.
select deptno1,count(*) from student group by deptno1 order by 1;

예시 : 
 3. 학년별, 전체인원수, 지도교수가 배정된 인원수를 조회하기.
select grade, count(*) 전체인원수, count(profno) 배정된인원수  
from student group by grade order by 1;

예시 :
 4. 교수 중 직급별(position)별 교수의 인원수 조회하기. 인원수가 많은 순으로 정렬하기.
select position, count(*) from professor group by position order by count(*) desc;

select position, count(*) from professor group by position order by 2 desc;

---

문제 :
 1. 학생의 학년별 키와 몸무게 평균 출력하기. 학년별로 정렬.
select grade,count(*),avg(height),avg(weight) from student group by grade order by 1;

문제 :
 2. 교수들에게 지급되는 부서별 연봉의합과 평균 구하기.
      연봉 :  급여*12 + 보너스
      합과 평균은 세자리마다 ',' 표시하고, 반올림하여 정수로 출력하기
      만약 보너스가 없는 경우 0으로 처리한다.
select deptno, to_char(sum(pay*12+nvl(bonus,0)),'99,999') 연봉의합,
to_char(round(avg(pay*12+nvl(bonus,0))),'99,999') 연봉의평균 
from professor group by deptno order by 1;

문제 : 
 3. 주민번호를 기준으로 여학생의 최대키, 최소키, 평균키를 출력하기.
  주민번호의 7자리가 1 : 남학생, 2 : 여학생.
select decode(substr(jumin,7,1),1,'남학생',2,'여학생') 성별, max(height),min(height), 
round(avg(height),2) from student where substr(jumin,7,1)= 2 group by substr(jumin,7,1)        

select max(height),min(height), round(avg(height),2) 
from student where substr(jumin,7,1)= 2

문제 : 
 4. 고객테이블에서 주민번호 기준 월별 인원수 조회하기. 
select substr(jumin,3,2) , count(*)인원수 from gogak 
group by substr(jumin,3,2) order by 1

문제 : 
 5. 고객테이블에서 주민번호 기준 성별 인원수 조회하기.
select decode(substr(jumin,7,1),1,'남학생',2,'여학생') 성별, 
count(substr(jumin,7,1))성별인원수 from gogak group by substr(jumin,7,1);

select case substr(jumin,7,1) when '1' then '남성' when '2' then '여성' end 성별, 
count(*) from gogak group by substr(jumin,7,1) order by 성별;

문제 : 
 6. 학생테이블에서 전화번호의 지역번호 기준으로 학생 수 출력하기.
select distinct substr(tel,1,instr(tel,')')-1) 지역번호,count(*)학생수 
from student group by substr(tel,1,instr(tel,')')-1);

select substr(tel,1,instr(tel,')')-1) 지역번호,count(*)학생수 
from student group by substr(tel,1,instr(tel,')')-1);