그룹으로 나누기 (GROUP BY)

1. COUNT로 행 개수 구하기

• 함수는 어떤 값을 입력하면 그에 대응하는 결과 값을 출력하는 작업을 수행하는 상자 같은 것이라고 생각하면 된다.
• 함수를 사용할 때 괄호() 안에 입력 데이터를 지정하는데 이를 인수 혹은 파라미터라고 하며, 출력 결과값을 반환 값이라고 한다.
• COUNT 함수() 안에 행 수를 알고 싶은 컬럼을 넣으면 된다.

• SELECT COUNT(*) FROM player player테이블의 모든 행(ROW)수 구하기

• COUNT(*)를 지정한 경우에는 테이블 전체 행수를 반환한다. (NULL값 포함)
• COUNT(컬럼)을 지정하면 해당 컬럼에 NULL값이 들어있는 행은 제외하고 NULL이 아닌 행의 개수만 반환한다.
• 동일한 테이블을 대상으로 COUNT함수를 사용해도 인수가 다르면 결과가 다르게 나오기때문에 주의를 해야 한다.

 

2. SUM으로 합계 구하기

• SUM 함수를 사용해 집합의 합계를 구할 수 있다.
• 예를들어 3개의 행(Row)의 특정 열(Column)의 값이 각각 1,2,3 일때, SUM 함수로 해당 컬럼을 지정하면 1+2+3으로 계산하여 6이라는 값을 반환한다.
• SUM 함수에 지정되는 컬럼은 숫자형만 사용할 수 있다.
• 문자형, 날짜형 자료는 합계를 구할 수 없기 때문에 player_name같은 문자형 자료에 SUM(player_name)으로 지정하면 오류가 발생한다.
• SUM 함수도 COUNT함수에 마찬가지로 NULL값을 무시한다. 즉, NULL값을 제거한 뒤에 합계를 구한다.

 

3. AVG로 평균 구하기

• SUM 집계함수를 사용하여 합계를 구할 수 있다.
• 이때 합한 값을 개수로 나누면 평균값을 구할 수 있다. (SUM(컬럼) / COUNT(컬럼)과 같이 지정하면 된다.)
• SUM, COUNT함수를 이용하지 않고 AVG함수를 이용해 평균값을 간단하게 구할 수 있다.

• SELECT AVG(height) FROM player; player테이블에서 키(height)의 평균을 구한다.

• AVG함수에 사용하는 인수도 SUM과 동일하게 숫자형 자료만 가능하다.
• AVG함수도 NULL값은 무시한다. NULL값을 제거한 뒤 평균을 계산한다.

 

4. MIN/MAX로 최소값/최대값 구하기

• MIN()와 MAX()함수를 사용해 최소값과 최대값을 구할 수 있다.

• SELECT MAX(height), MIN(height) FROM player; player테이블에서 가장 큰 키와 가장 작은키를 추출한다.

• MIN(), MAX() 함수는 숫자형 자료뿐 아니라 문자형, 날짜형 자료에도 사용할 수 있다.

• SELECT MIN(birth_date) FROM player; player테이블에서 생년월일이 가장 빠른 날짜를 추출한다.

• 이 두 함수도 NULL값을 무시하고 처리한다.

 

5. GROUP BY로 그룹나누기

• 테이블을 몇 개의 그룹으로 나누어서 집계작업을 수행하도록 하자. 예를 들어 상품자료를 상품 종류별로, 선수자료를 소속 팀별로 확인하는 것이다.
• 우리 말로 ~마다, ~별, ~단위 등으로 표현되는 것들이다. 이 작업을 수행하기 위해 GROUP BY를 사용한다.
• GROUP BY를 사용하면 GROUP BY뒤에 사용한 컬럼으로 그룹핑되어 자동으로 각 그룹에 대해 집계함수가 적용된다.

• SELECT 그룹 나눌 기준 컬럼, 그룹함수1, 그룹함수2 FROM 테이블명 GROUP BY 그룹 나눌 컬럼;

• 집계함수 계산을 수행할 때 NULL값을 제외시키고 처리하지만 GROUP BY에 사용한 컬럼에 NULL값이 있는 경우 NULL값도 별도의 그룹으로 처리된다.
• 집계 함수 처리에 인자로 사용된 컬럼을 제외한 SELECT에 있는 모든 컬럼들은 GROUP BY절에 존재해야 한다.

 

6. 여러 컬럼으로 그룹 나누기

• 매출 분석을 할 때 어느 부서의 매출합계가 얼마인지 조회하고 싶으면 GROUP BY절 기준을 부서로 지정하면 된다.
• 좀 더 세밀하게 나누어서 조회하고 싶은 경우 GROUP BY절에 여러 항목을 지정한다.
• 예를 들어 매출년도별 각 부서의 매출합계가 얼마인지 조회하고 싶다면 GROUP BY절에 매출년도, 부서 2개 항목을 지정해야 한다.

• SELECT 매출년도, 부서, SUM(매출합계) FROM 매출표 GROUP BY 매출년도, 부서

 

7. WHERE 와 GROUP BY 함께 사용하기

• GROUP BY절을 WHERE절과 함께 사용하는 경우 WHERE절 뒤에 사용한다.
• WHERE에 지정한 조건을 만족하는 자료를 먼저 추출한 다음 GROUP BY에 사용한 컬럼을 기준으로 분류작업(그룹을 나누는 작업)을 수행하게 된다.

• SELECT 그룹기준컬럼, 그룹함수1, 그룹함수2, ... FROM 테이블명 WHERE 조건식 GROUP BY 그룹기준컬럼;

 

8. HAVING절 이용하기

• GROUP BY로 나눈 그룹 중 어떤 그룹을 포함하고 어떤 그룹을 배제할 것인지 필터링 하고 싶은 경우 HAVING을 사용한다.
• 지점 매출평균금액이 1억이 넘는 지점의 명단만 원한다고 가정했을 때 개별 행이 아니라 지점별 그룹화한 결과를 기반으로 조건 검사를 해야한다. 이 경우 아래와 같이 HAVING절에 매출평균금액을 확인하는 조건을 지정한다.

• SELECT 지점명, AVG(매출) FROM 매출표 GROUP BY 지점명 HAVING AVG(매출) > 100000000

• WHERE절은 행을 필터링 하는 용도로 사용하기 때문에 그룹 결과를 지정할 수 없다.

 

9. 그룹 결과 정렬하기

• GROUP BY로 그룹화해도 실행결과가 정렬된 형태로 나오지는 않는다.
• 그룹화된 자료들을 특정 기준으로 정렬된 형태로 조회하고 싶은 경우 별도로 ORDER BY절을 지정하면 된다.

 

10. 예제

• player테이블 각 팀에서 팀별로 최대키, 최소키, 최대몸무게, 최소몸무게 조회

• SELECT team_id, MAX(height), MIN(height), MAX(weight), MIN(weight) FROM player GROUP BY team_id;

• player테이블 GK포지션 선수중 키가 185보다 큰 선수가 각 팀에 몇명있는지 조회

• SELECT team_id, COUNT(*) FROM player WHERE position='GK' AND height> 185 GROUP BY team_id;

• player테이블 소속선수들 평균키가 180보다 큰 포지션에 대해 포지션, 인원, 평균키를 조회

• SELECT position, COUNT(*), AVG(height) FROM player GROUP BY position HAVING AVG(height) > 180;

• player테이블에서 키 185이상인 선수가 3명이상 있는 팀만 팀id를 조회

• SELECT team_id FROM player WHERE height >= 185 GROUP BY team_id HAVING COUNT(*) > 3;