06. 집계함수(GROUP BY와 함께 사용)

GROUP BY와 집계함수는 함께 쓰이는 경우가 많음

 

기본형식

SELECT 집계함수 FROM 테이블 GROUP BY 그룹 대상(열);

예시

SELECT SUM(salary) FROM user GROUP BY team;

 

user 테이블에서 team 열을 이용하여 그룹을 생성한다. 그리고 각 그룹(team)의 salary라는 값의 총 합을 구하는 쿼리이다.

 

 

 

GROUP BY와 함께 쓰이는 집계 함수

• AVG(field) = 그룹의 field(값)에 대한 평균을 구함
• MIN(field) = 그룹의 fiel(값)d에 대한 최소값을 구함
• MAX(field) = 그룹의 field(값)에 대한 최대값을 구함
• STDEV(field) = 그룹의 field(값)에 대한 표준편차를 구함
• VAR_SAMP(field) = 그룹의 field(값)에 대한 분산을 구함

 

COUNT 함수 사용 비교

• COUNT(*) = 그룹의 행의 수를 구함
• COUNT(DISTINCT 필드명)
  - 그룹의 행의 수를 구하는데 필드값이 중복될 때 1개만 카운트 함
• COUNT(CASE WHEN field = ‘x’ THEN 1 END)
  - 그룹의 행의 수를 구하는데 field(값)이 x 일 때 1을 카운트 함

 

예시:아래와 같은 데이터가 있다고 가정

 

SELECT part, module FROM module
WHERE part = 103

 

Query Result
>> part, module
>> 103, A
>> 103, A
>> 103, B
>> 103, B
>> 103, C

 

COUNT(*)

 - part가 103인 데이터(행)의 수를 구함

 

SELECT part, COUNT(*) AS ‘CNT’ FROM module
WHERE part = 103
GROUP BY part ;

 

Query Result
>> part, CNT
>> 103, 5

COUNT(DISTINCT 필드명)

- part가 103인 데이터 중 module의 종류(중복 제거)를 구함
- A,B,C 3개

 

SELECT part, COUNT(DISTINCT module) AS ‘CNT’ FROM module
WHERE part = 103
GROUP BY part ;

 

Query Result
>> part, CNT
>> 103, 3

COUNT(CASE WHEN 조건 THEN 카운트[=숫자] END)

- part가 103인 데이터 중 module이 각각 A, B, C인 수를 구함

- A = 2개, B = 2개, C = 1개 출력

 

SELECT 
part,
COUNT(CASE WHEN module = ‘A’ THEN 1 END) AS ‘CNT_A’ ,
COUNT(CASE WHEN module = ‘B’ THEN 1 END) AS ‘CNT_B’ ,
COUNT(CASE WHEN module = ‘C’ THEN 1 END) AS ‘CNT_C’
FROM module
WHERE part = 103
GROUP BY part ;

 

Query Result
>> part, CNT_A, CNT_B, CNT_C
>> 103, 2, 2, 1

집계함수 사용 예시

01. GROUP BY + 2개 이상 집계 함수 사용

 

SELECT team, MAX(height), MIN(height), AVG(height) FROM user
GROUP BY team

 

Query Result
>> team, MAX(height), MIN(height), AVG(height)
>> 레알 마드리드, 196, 167, 179
>> FC바르셀로나, 187, 165, 178
>> …

 

02. 데이터에서 최대값과 최소값에 해당하는 데이터 출력

- 최대값과 최소값에 해당하는 name과 height 값 출력

 

SELECT name, height
FROM user
WHERE team = ‘레알 마드리드’
AND
height = (SELECT MAX(height) FROM user WHERE team = ‘레알 마드리드’)
AND
height = (SELECT MIN(height) FROM user WHERE team = ‘레알 마드리드’) ;

 

Query Result
>> name, height
>> COUTORIS, 200
>> MODRIC, 172
>> …

 

 

GROUP BY + HAVING

WHERE 절에는 집계함수를 사용할 수 없음: Invalid use of group function

예시: 아래와 같은 데이터가 있다고 가정

 

SELECT 
team, name, contract_year, salary
FROM user

 

Query Result
>> team, name, contract_year, salary
>> 레알 마드리드, MODRIC, 2, 100
>> 레알 마드리드, BENZEMA, 2, 150
>> 레알 마드리드, BALE, 1, 300
>> 레알 마드리드, RODRIGO, 5, 50
>> FC 바르셀로나, DE JONG, 3, 100
>> FC 바르셀로나, PIQUE, 2, 150
>> FC 바르셀로나, DEPAY, 3, 200
>> A.마드리드, GRIEZMANN, 4, 150
>> A.마드리드, KOKE, 2, 100

 

WHERE 절에 집계함수가 포함될 경우
- 예: (contract_year * salary) 값의 합이 1000인 team을 출력하는 쿼리

- WHERE 절에서는 집계함수를 쓸 수 없기 떄문에 오류가 출력됨

 

SELECT 
team, SUM(contract_year * salary) AS ‘SUM’
FROM user
WHERE SUM(contract_year * salary) > 1000
GROUP BY team ;

 

QUERY Result
>> Invalid use of group function

 

 

WHERE 절에서 집계함수를 쓸 수 없기 때문에 HAVING을 사용함
위치: HAVING은 GROUP BY 뒤에 작성

 

SELECT 
team, SUM(contract_year * salary) AS ‘SUM’
FROM user
GROUP BY team
HAVING SUM(contract_year * salary) > 1000 ;

 

QUERY Result
>> team, SUM
>> 레알 마드리드, 1050