[SQL] 집계함수(aggregate function), GROUP BY, HAVING

 

집계함수란?

• 집계 함수는 값들의 집합을 계산해서 하나의 값을 리턴하는 함수입니다
• COUNT(*)를 제외하고 NULL 값을 무시합니다
• SELECT문에서 GROUP BY절과 함께 사용됩니다
• WHERE절에는 집계함수를 사용할 수 없고 GROUP BY 후 HAVING절에 집계함수를 사용해야 한다

 

1) COUNT

• 특정 열의 갯수를 반환합니다
• COUNT(*) 로 작성하면 테이블에 존재하는 행의 갯수 반환
• COUNT(특정 열) : NULL값을 제외한 특정 열의 갯수 반환

# NULL을 제외한 EmployeeId의 행 갯수 반환
SELECT COUNT(EmployeeId)
FROM Employees;

 

2) MAX, MIN

특정 열의 최댓값과 최솟값을 반환합니다

-- 가장 어린 사람의 생년월일
SELECT MAX(BirthDate)
FROM Employees;
-- 가장 나이가 많은 사람의 생년월일
SELECT MIN(BirthDate)
FROM Employees;

 

3) SUM

특정 열의 합계를 구합니다

SELECT SUM(Price)
FROM Products;

 

4) AVG

특정 열의 평균을 구합니다

SELECT AVG(Price)
FROM Products;

 

IFNULL

SUM()과 AVG()의 경우 NULL값을 무시하고 연산하게 됩니다. 만약 NULL값을 포함하고 계산하고 싶다면 IFNULL()을 사용해 NULL값을 다른 값으로 대체해야 합니다

 

IFNULL(컬럼명, NULL일 경우 대체 값)

SELECT COUNT(IFNULL(number, 0))
FROM table12;

 

예제

# 1번 고객이 1997년 중에 총 소비한 금액은?
SELECT SUM(p.Price * od.Quantity)
FROM Orders o
         JOIN OrderDetails od
              ON o.OrderID = od.OrderID
         JOIN Products p
              ON p.ProductID = od.ProductID
WHERE o.CustomerID = 1
  AND o.OrderDate BETWEEN '1997-01-01' AND '1997-12-31';

# 멕시코 고객들이 1997년에 소비한 금액은?
SELECT SUM(p.Price * od.Quantity)
FROM Products p
         JOIN OrderDetails od
              ON p.ProductID = od.ProductID
         JOIN Orders o
              ON od.OrderID = o.OrderID
         JOIN Customers c
              ON o.CustomerID = c.CustomerID
WHERE c.Country = 'mexico'
  AND OrderDate BETWEEN '1997-01-01' AND '1997-12-31';

 

 

GROUP BY

SUM, COUNT, MAX 등의 집계 함수와 함께 사용하며 특정 열을 기준으로 집계 결과를 그룹화합니다 

 

예제

 

1) 카테고리별 상품수 조회

# 카테고리별 상품수 조회
SELECT c.CategoryID       번호,
       c.CategoryName     이름,
       COUNT(p.ProductID) 상품수
FROM Categories c
         JOIN Products p
              ON c.CategoryID = p.CategoryID
GROUP BY c.CategoryID;

 

카테고리별 상품수 조회

 

2) 1997년에 매출액이 가장 높은 직원 조회

SELECT e.EmployeeId,
       e.LastName,
       e.FirstName,
       SUM(od.Quantity * p.Price) 매출액
FROM Orders o
         JOIN OrderDetails od
              ON o.OrderID = od.OrderID
         JOIN Products p
              ON od.ProductID = p.ProductID
         JOIN Employees e
              ON o.EmployeeID = e.EmployeeID
WHERE OrderDate BETWEEN '1997-01-01' AND '1997-01-31'
GROUP BY e.EmployeeID
ORDER BY 매출액 DESC;

 

3) 국가별, 상품별 주문 수량

SELECT p.ProductName 상품판매수,
       c.Country     국가,
       SUM(Quantity) 수량
FROM OrderDetails od
         JOIN Orders o ON od.OrderID = o.OrderID
         JOIN Customers c ON c.CustomerID = o.CustomerID
         JOIN Products p ON p.ProductID = od.ProductID
GROUP BY c.Country, p.ProductID
ORDER BY 국가, 수량 DESC;

 

차트 조회 결과 일부

 

 

HAVING

 

집계함수는 WHERE 절에서는 사용할 수 없고 HAVING 절을 사용해야 합니다

즉, HAVING은 집계함수의 조건절입니다

 

예제

1) 상품 수가 10개 이상인 카테고리

SELECT Products.CategoryID, COUNT(ProductID)
FROM Products
GROUP BY CategoryID
HAVING COUNT(ProductID) >= 10;

 

2) 고객이 5명 이상인 국가들 내림차순으로 조회

ELECT Country, COUNT(CustomerID) 고객수
FROM Customers
GROUP BY Country
HAVING 고객수 >= 5
ORDER BY 고객수 DESC;

 

3) 97년 7월 매출액이 10000달러 이상인 직원들 내림차순으로 조회

SELECT e.LastName, e.FirstName, SUM(p.Price * od.Quantity) sumOfOrders
FROM Employees e
         JOIN Orders o ON o.EmployeeID = e.EmployeeID
         JOIN OrderDetails od ON od.OrderID = o.OrderID
         JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderDate BETWEEN '1997-07-01' AND '1997-07-31'
GROUP BY e.EmployeeID
HAVING sumOfOrders >= 10000
ORDER BY sumOfOrders DESC;