[문과 코린이의 IT 기록장] MSSQL - 집계 함수, GROUP BY / HAVING

[문과 코린이의 IT 기록장] MSSQL - 집계 함수, GROUP BY / HAVING

[문과 코린이의 IT 기록장] MSSQL - 집계 함수, GROUP BY / HAVING

 

---

< 참고 강의 영상 >

MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.2 대시보드 - 인프런 | 강의 (inflearn.com)

MSSQL Server 2016 기반의 데이터베이스 입문에서 활용까지 Part.2 - 인프런 | 강의

2022.01.20 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - 데이터베이스 변경 (Datatype Alteration, Table Structure , UPDATE, 제약조건, ISNULL example, 데이터베이스 생성/삭제, 유니코드 형식 데이터 입력)

[문과 코린이의 IT 기록장] MSSQL - 데이터베이스 변경 (Datatype Alteration, Table Structure , UPDATE, 제약조

2022.01.21 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - CONSTRAINT 예제, IDENTITY, SELECT 쿼리의 기본 종합정리

[문과 코린이의 IT 기록장] MSSQL - CONSTRAINT 예제, IDENTITY, SELECT 쿼리의 기본 종합정리

2022.01.21 - [문과 코린이의, [SQL] 기록] - [문과 코린이의 IT 기록장] MSSQL - 비교연산자, 논리연산자

[문과 코린이의 IT 기록장] MSSQL - 비교연산자, 논리연산자

---

1. 집계 함수

- 전체 레코드가 몇 개인지, 전체 합이 얼마인지, 평균이 얼마인지 등을 구해준다.

- 즉 각각의 행을 하나로 묶어서, 하나의 단일 값을 반환해준다.

 ex. AVG(평균), COUNT(개수), MAX(최댓값), MIN(최솟값), SUM(합계)

 

1) AVG 함수

ex. titles 테이블의, 책 가격들에 대해 평균을 출력하시오.

SELECT AVG(price) FROM dbo.titles;
-- 이 또한 NULL은 제외하고 계산한다.

 

2) COUNT 함수

ex 1. titles 테이블의, 책들에 대해 총 갯수를 출력하시오

SELECT COUNT(*) FROM dbo.titles;

ex 2. titles 테이블의 책 값이 있는 것에 대해서만 갯수를 출력하시오.

 * 책 값이 결정되지 않은 것들(NULL)은 계산하지 않음.

SELECT COUNT(price) FROM dbo.titles; -- NULL은 제외하고 계산함.

 

3) NULL값

- NULL은 검색시 중요한 변수로 작용한다.

ex 1. 테이블의 책 값 평균을 구하시오.

SELECT AVG(price) FROM dbo.titles;
-- NULL값 고려 x

ex 2. 테이블의 책 값이 있는 책들의 평균을 구하시오.

/* NULL값 고려 O */
-- 모든 레코드를 count함수가 반환
SELECT SUM(price) / COUNT(*) FROM dbo.titles;

-- price값이 있는 레코드만 count함수가 반환
SELECT SUM(price) / COUNT(price) FROM dbo.titles; -- 입력된 책 값들의 평균 (정확한 평균)

 

- NULL 값을 제외시키는 쿼리 구성 방법

/* example */
SELECT COUNT(*) FROM dbo.titles WHERE price IS NOT NULL; -- null이 아닌 것들만 가져와라.
SELECT SUM(price) / COUNT(*) FROM dbo.titles WHERE price IS NOT NULL; -- null이 아닌 것들의 값들만 가져와서 나누기

 

4) MAX , MIN 함수

SELECT MAX(price) FROM dbo.titles;
SELECT MIN(price) FROM dbo.titles;

 

---

2. GROUP BY, HAVING

- 집계 함수와 함께 많이 사용되는 절이 GROUP BY절이다.

 

1) GROUP : 특정 컬럼(열)을 기준으로 집계를 낸다는 뜻이다.

SELECT [GROUP BY절의 지정 컬럼],[집계할 값]
FROM [테이블명]
GROUP BY [그룹 묶을 컬럼];

ex 1. titles 테이블에서, 각 type별로 카테고리를 그룹지어서 출력하시오.

SELECT type, AVG(price) AS 평균 -- type과 AVG(price) 출력
FROM dbo.titles
GROUP BY type;
ORDER BY AVG(price) DESC; 
  -- 집계함수 사용 시, 이와 같이 정렬을 수행. (컬럼명만 사용 X)
  -- price만 치면 에러 발생

ex 2. 

SELECT type AS t, COUNT(type) AS '그룹 별 책 갯수', AVG(price) AS ap 
FROM dbo.titles
GROUP BY type
ORDER BY ap DESC; -- ap = AVG(price)

 

ex 3. 평균 책 값이 가장 큰 TOP 3 출력 - 평균 책 값 정수형으로 변환 (WITH TIES 사용)

SELECT TOP 3 WITH TIES type AS t, COUNT(type) AS '그룹별 책 갯수', CONVERT(INT, AVG(price)) AS ap
FROM dbo.titles
GROUP BY type
ORDER BY ap DESC;

 

 

2) HAVING : GROUP BY를 사용할 때 조건을 줄 경우 사용한다.

- HAVING은 GROUP BY로 가져온 결과값에 대해서, 조건을 붙여 원하는 값만 출력시킬 수 있다.

 ex. 각 카테고리별로 그룹을 묶어서 출력 시, 평균 책값이 15달러 이상만 출력하시오.

SELECT type, AVG(price) AS '각 카테고리별 평균 책값'
FROM dbo.titles 
GROUP BY type
HAVING AVG(price)>=15;

※ HAVING절은 GROUP BY절 뒤에 쓴다. (GROUP BY절로 그룹지어 가져온 결과 값에 대해 조건을 붙여주는 것이기 때문)

 

3) WHERE절과 함께 사용

- WHERE절과 HAVING절을 같이 사용할 때는 주의해야 한다.

- WHERE > GROUP BY > HAVING절의 순서로 기억하자.

 

ex. 책 가격이 11달러 이상에서, 각 카테고리 타입별로 묶어서, 평균 가격이 17달러 이상만 출력해라. (정렬은 평균가격으로 오름차순)

SELECT type, AVG(price) AS '각 카테고리별 평균 책 값'
FROM dbo.title
WHERE price>=11 -- 책 값이 11달러 이상만 추려냄
GROUP BY AVG(price) -- 추려낸 것들에서 각 타입별로 그룹을 묶어줌
HAVING>=17; -- 묶은 그룹별로 조건을 또 붙인다.
ORDER BY AVG(price) ASC;

 

4) GROUP BY ALL

- GROUP BY

: 각 카테고리별 평균을 구할 때, 조건에 맞는 값이 없는 경우도 나올 수 있다. 그러면 NULL값을 가지게 되는데, 일반적으로 GROUP BY는 그러한 NULL을 출력하지 않는다.

 

- GROUP BY ALL

: ALL은 조건에 맞는 값이 없는 경우에도, NULL을 그대로 출력시켜준다. 따라서 ALL을 사용하면 조건에서 어떤 항목이 제외되었는지를 명확하게 알 수 있게 된다.

SELECT type, AGV(price) AS 평균가격
FROM dbo.titles
WHERE price>=21
GROUP BY ALL type; -- price에 값이 없는 경우에도 null값으로 출력

 

ex 1. titles에서 카테고리별 평균 책값을 출력하시오. 단 아래 카테고리는 제외하시오.

 * 제외 카테고리 : mod_cook, psychology, trad_cook, UNDECIDED

SELECT type, AVG(price) AS 평균가격
FROM dbo.titles
WHERE type NOT IN('mod_cook','psychology', 'trad_cook', 'UNDECIDED')
GROUP BY type 
ORDER BY AVG(price) DESC;

 

ex 2. 각 카테고리별 평균 책값을 출력시 위에서 제외된 카테고리도 모두 출력하시오.

SELECT type, AVG(price) AS 평균가격
FROM dbo.titles
WHERE type NOT IN('mod_cook','psychology', 'trad_cook', 'UNDECIDED') -- 카테고리가 탈락됨 (원래 값이 있더라도 NULL값으로 지정됨)
GROUP BY ALL type -- 아에 제외된 쿼리들(NULL)값이지만, NULL로 값이 출력되도록 함.
ORDER BY AVG(price) DESC;

 

---

* 유의사항 - 아직 공부하고 있는 문과생 코린이가, 정리해서 남겨놓은 정리 및 필기노트입니다. - 정확하지 않거나, 틀린 점이 있을 수 있으니, 유의해서 봐주시면 감사하겠습니다. - 혹시 잘못된 점을 발견하셨다면, 댓글로 친절하게 남겨주시면 감사하겠습니다 :)