[DB]5. GROP BY&HAVING절

[데이터베이스] GROUP BY&HAVING절

 

1. GROUP BY:  특정 컬럼을 기준으로 그룹화하여 집계할 때 사용, 행을 그룹화하여 각각 단일행으로 표기

       SELECT FROM GROUP BY 컬럼명

• 실제 출력되는 행의 개수가 감소하기 때문에 GROUP BY보다 늦게 출력되는 절(SELECT, HAVING, ORDER BY)에서 출력할 수 있는 행이 제한됨
• 그룹화한 컬럼만  SELECT에서 사용할 수 있음
• 그룹화했을 때 행의 개수와 SELECT문에서 출력하는 컬럼의 행의 개수가 같아야 함

 

• 대신 집계함수(SUM, COUNT, MIN, MAX, AVG..)로 처리하는 컬럼은 가능하다.

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

SELECT SUM(SALARY)
FROM EMPLOYEES;

   -다중행 함수: (문자, 숫자, 날짜 함수...): 함수에 적용되는 행은 1개, 결과값은 각각의 행으로 도출 

SELECT UPPER(FIRST_NAME)
FROM EMPLOYEES;

• COUNT(컬럼): NULL 제외하여 COUNT
  COUNT(*): NULL 포함하여 COUNT
  다른 집계함수는 * 사용불가 예) SUM(*)(X)

SELECT DEPARTMENT_ID, COUNT(*), COUNT(DEPARTMENT_ID)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

 

 

 

[실습1]

--실습1.
--성적표 테이블에 학생별로 평균점수 출력
--단. 소수점 첫째자리 까지만 출력

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

[실습2]

--실습2. 과목별로 최고 성적과 최저 성적 출력
SELECT 과목, MAX(성적) AS "최고성적", MIN(성적) AS "최저성적" 
FROM 성적표
GROUP BY 과목;

[실습3]

--실습3. 교육생 정보 테이블에서 각 팀에 몇 명이 있는지 출력
SELECT 팀, COUNT(*), COUNT(학생ID)
FROM 교육생정보
GROUP BY 팀;

[실습4]

--실습4. 성적표 테이블에서 학생별로 JAVA와 DATABASE 성적의 평균 출력
--단, 소수점 첫째 자리에서 반올림

SELECT 학생ID, ROUND(AVG(성적), 1)
FROM 성적표
WHERE 과목 != 'PYTHON'
GROUP BY 학생ID;

 

 

2. HAVING : GROUP BY절을 통해 그룹화된 결과 중에서 그룹에 대한 조건에 따라 원하는 결과로 필터링, GROUP BY와 함께 사용(단독으로 사용할 수 없음)

• 집계함수에 대한 조건은 WHERE절이 아니라 HAVING절에 쓴다.
• HAVING절에 쓰는 컬럼은 GROUP BY에 있는 컬럼만 가능함
• WHERE절에서 집계함수를 사용하지 못함>WHERE-GROUP BY-HAVING 순서로 실행되기 때문
• 집계함수는 그룹화한 다음에 사용할 수 있음

 

[실습 5]

--교육생 정보에서 소속된 팀의 인원 수가 3명 이상인 팀만 출력
SELECT 팀, COUNT(학생ID)
FROM 교육생정보
GROUP BY 팀
HAVING COUNT(학생ID)>=3;

[실습 6]

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

SELECT DEPARTMENT_ID, MAX(SALARY*12) AS ANNSAL
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY*12)>=100000;

[실습 7]

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

SELECT 학생ID, ROUND(AVG(성적),1) AS 평균성적
FROM 성적표
GROUP BY 학생ID
HAVING 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-SELECT, ORER BY

• 컬럼이름 외에 숫자나 별칭으로 지정가능

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;

컬럼 순서 1: DEPARTMENT_ID, 2: SUM(SALARY)