SQL GROUP BY와 HAVING와 ORDER BY

1. GROUP BY

- 특징 칼럼을 기준으로 그룹화할 때 사용

- 부서별 급여의 평균을 구하고 싶을 때

SELECT AVG(SALARY)
FROM EMPLOYEES
WHERE department_id = 100;

SELECT AVG(SALARY)
FROM EMPLOYEES
WHERE department_id = 90;

SELECT AVG(SALARY)
FROM EMPLOYEES
WHERE department_id = 80;

 

1.1 그룹화(GROUP BY)

- 부서별 급여의 평균을 반올림

SELECT department_id, ROUND(AVG(SALARY))
FROM EMPLOYEES
GROUP BY department_id;

 

- 실행 불가

-- 실행 불가
SELECT employee_id, department_id, ROUND(AVG(SALARY))
FROM EMPLOYEES
GROUP BY department_id;

- FROM GROUP BY 다음에 SELECT가 순서로 출력이 됙 ㅣ때문에, 실제 출력 되는 행의 개수가 감소(108개 에서 12개)  
- 이미 그룹화시킨 행의 개수(12개)와 출력하고자 하는 EMPLOYEE_ID 행의 개수(108개) 맞지 않기 때문에 출력이 불가능하다.

 

1.2. 집계함수

- SELECT절에서 사용 가능

- sum, min, max, avg 등등

SELECT department_id,SUM(salary), ROUND(AVG(SALARY))
FROM EMPLOYEES
GROUP BY department_id;

 

- 다중행 함수(집계함수..) : 함수에 적용되는 행은 여러 개, 결과값은 1개의 행으로 도출

SELECT sum(salary)
FROM employees;

- 단일행 함수(문자, 숫자, 날짜 함수 등..) : 함수에 적용되는 행이 1개, 각 행에 대한 결과값으로 도출

SELECT first_name, UPPER(first_name)
FROM employees;

 

1.3. 실습

DROP TABLE 데이터디자인 ;
DROP TABLE 교육생정보 ; 
DROP TABLE 성적표 ; 

CREATE TABLE 교육생정보 (
학생ID VARCHAR2(9) PRIMARY KEY , 
학생이름 VARCHAR2(50) NOT NULL , 
팀 VARCHAR2(5) 
); 

CREATE TABLE 성적표 ( 
    학생ID VARCHAR2(9) , 
    과목   VARCHAR2(30) , 
    성적   NUMBER  , 
    CONSTRAINT PK_성적표 PRIMARY KEY(학생ID , 과목) , 
    CONSTRAINT FK_성적표 FOREIGN KEY(학생ID) REFERENCES 교육생정보(학생ID) 
)  ; 

INSERT INTO 교육생정보 VALUES ('SMHRD1' , '박수현' , 'A') ; 
INSERT INTO 교육생정보 VALUES ('SMHRD2' , '송승호' , 'A') ; 
INSERT INTO 교육생정보 VALUES ('SMHRD3' , '이은비' , 'B') ; 
INSERT INTO 교육생정보 VALUES ('SMHRD4' , '박원호' , 'B') ; 
INSERT INTO 교육생정보 VALUES ('SMHRD5' , '000' , 'B') ; 
INSERT INTO 교육생정보 VALUES ('SMHRD6' , '000' , 'C') ; 
INSERT INTO 교육생정보 VALUES ('SMHRD7' , '000' , 'C') ; 
INSERT INTO 교육생정보 VALUES ('SMHRD8' , '000' , 'C') ; 
INSERT INTO 교육생정보 VALUES ('SMHRD9' , '000' , 'C') ; 

INSERT INTO 성적표 VALUES('SMHRD1'  ,'JAVA' , 90); 
INSERT INTO 성적표 VALUES('SMHRD1'  ,'DATABASE' , 85); 
INSERT INTO 성적표 VALUES('SMHRD1'  ,'PYTHON' , 100); 
INSERT INTO 성적표 VALUES('SMHRD2'  ,'JAVA' , 100); 
INSERT INTO 성적표 VALUES('SMHRD2'  ,'DATABASE' , 100); 
INSERT INTO 성적표 VALUES('SMHRD2'  ,'PYTHON' , 20); 
INSERT INTO 성적표 VALUES('SMHRD3'  ,'JAVA' , 100); 
INSERT INTO 성적표 VALUES('SMHRD3'  ,'DATABASE' , 100); 
INSERT INTO 성적표 VALUES('SMHRD3'  ,'PYTHON' , 20); 
INSERT INTO 성적표 VALUES('SMHRD4'  ,'JAVA' , 85); 
INSERT INTO 성적표 VALUES('SMHRD4'  ,'DATABASE' , 40); 
INSERT INTO 성적표 VALUES('SMHRD4'  ,'PYTHON' , 60); 
INSERT INTO 성적표 VALUES('SMHRD5'  ,'JAVA' , 100); 
INSERT INTO 성적표 VALUES('SMHRD5'  ,'DATABASE' , 100); 
INSERT INTO 성적표 VALUES('SMHRD5'  ,'PYTHON' , 100); 
INSERT INTO 성적표 VALUES ( 'SMHRD6' , 'JAVA' , NULL ) ; 
INSERT INTO 성적표 VALUES ( 'SMHRD6' , 'DATABASE' , NULL ) ; 
INSERT INTO 성적표 VALUES ( 'SMHRD6' , 'PYTHON' , NULL ) ;

- 실행 후 진행

 

- 학생별로 평균 점수 구하기(소수점 1자리)

SELECT 학생id, ROUND(AVG(성적),1) as 평균점수
FROM 성적표
GROUP BY 학생id;

 

1.4 Count(칼럼)과 Count(*) 차이점

- Count(컬럼) : NULL을 제외한 카운트
- Count(*) : NULL을 포함한 카운트
- 다른 집계함수는 * 사용 불가능

SELECT DEPARTMENT_ID, COUNT(*), COUNT(department_id)
FROM employees
GROUP BY department_id;

 

1.5 실습

- 과목별로 최고 성적과 최저 성적을 출력

SELECT 과목, max(성적), min(성적)
FROM 성적표
GROUP BY 과목;

- 수강생 정보 테이블에서 각 팀에 몇 명이 있는지 출력

SELECT 팀, COUNT(*),COUNT(학생ID)
FROM 교육생정보
GROUP BY 팀;

- 성적표 테이블에서 학생별로 JAVA와 DATANASE 성적의 평균을 출력
- 단, 1의 자리에서 반올림

 

SELECT 학생id, ROUND(AVG(성적),1) as "JAVA DATANSE 평균"
FROM 성적표
WHERE 과목 not IN ('PYTHON')
GROUP BY 학생id;

SELECT 학생id, ROUND(AVG(성적),1) as "JAVA DATANSE 평균"
FROM 성적표
WHERE 과목 IN ('JAVA','DATANSE')
GROUP BY 학생id;

 

2. HAVING

- GROUP절을 통해서 그룹화된 결과 중에서 원하는 결과로 필터링하는 문법
- GROUP BY -> HAVING, HAVING절에 칼럼은 GROUP BY에서 칼럼만 가능!

SELECT 학생ID, ROUND(AVG(성적))
FROM 성적표
GROUP BY 학생ID
HAVING AVG(성적)<=75;

 

2.1 실습

- 수강색 정보에서 소속된 팀의 인원수가 3명 이상인 팀만 출력

FROM 교육생정보
GROUP BY 팀
HAVING COUNT(팀) >=3;

 

- 직원 테이블에서 부서별 최고 연봉 100,000 이상인 부서만 출력

SELECT manager_id, max(salary * 12) as 연봉
FROM employees
GROUP BY manager_id 
HAVING max(salary * 12) >=100000;

 

- 성적표 테이블에서 학생 별 평균성적을 출력하되, NULL이 아닌 값만 출력
- 단 성적은 1은 자리까지만 표시

SELECT 학생id, ROUND(AVG(성적)) as 평균성적
FROM 성적표
GROUP BY 학생id
HAVING ROUND(AVG(성적))is not null;

3. ORDER BY(오름차순 ASC, 내림차순 DESC)

- 여러 컬럼을 기준으로 정럴 가능

SELECT *
FROM 성적표
ORDER BY 학생ID, 성적 DESC;

 

- SELECT절에 입력되지 않는 컬럼으로 정렬 가능

SELECT SALARY
FROM employees
ORDER BY employee_id DESC;

 

 

- GROUP BY로 그룹화 된 경우, GROUP BY컬럼으로만 정렬 가능
- GROUP BY에 없는 EMPLOYEE_ID로는 정렬이 불가능하다.
- 실행 순서가 GROUP BY SELECT ORDER BY 순서 이기때문에 실행 불가능

SELECT DEPARTMENT_ID, SUM(SALARY)
FROM employees
GROUP BY department_id
ORDER BY employee_id;

-- 실행 불가 (오류)

 

- 별칭이나 숫자 사용해서 정렬 가능

SELECT DEPARTMENT_ID, SUM(SALARY) AS 급여
FROM employees
GROUP BY department_id
ORDER BY 급여;

-- 컬럼의 순서의 숫자로 입력해도 가능
SELECT DEPARTMENT_ID, SUM(SALARY) AS 급여
FROM employees
GROUP BY department_id
ORDER BY 2;