집계함수

이번에 다룰 주제는 집계 함수의 사용법입니다. SQL에서는 다양한 방법으로 데이터의 집계를 할 수 있습니다. 얼마나 많은 고객이 서울에 살고 있는가?

얼마나 많은 주문을 어느 고객이 했는가? 특정 고객의 상품 주문 총 금액은 얼마인가?

특정 고객이 9월달에 주문한 총 금액은 얼마인가?

등 데이터를 가지고 통계를 내기 위해서는 집계 함수의 사용법을 알아야 합니다. 그래서 이번 포스팅에서는 집계 함수를 사용하는 방법에 대해 알아보도록 하겠습니다.

단순히 테이블의 행수를 알고 싶으면 아래와 같이 Count 함수를 사용하면 됩니다.

SELECT COUNT(*) FROM Customers

 

위와 같이 Count(*) 함수를 사용하면 전체 행수를 알 수 있습니다. 이 경우 NULL 값은 무시됩니다. 만약 특정 컬럼(열)이 NULL 값을 포함하고 있는데 이 NULL 값을 제외하고 행수를 알고 싶다면 아래와 같이 Count 함수를 사용하면 됩니다.

SELECT COUNT(MiddleName) FROM Person.Contact

SELECT COUNT(*) FROM Person.Contact

 

Microsoft 사에서 무료로 제공해주는 AdventureWorks 데이테베이스를 이용해서 Count 함수를 사용해 보았습니다. 위 와 같이 Count 함수에 특정 컬럼(열)을 지정해서 사용하면 NULL 값은 제외한 출력결과를 확인할 수 있습니다. 열이름을 지정하지 않고 COUNT 함수를 사용하면 NULL 값은 무시되기 때문에 전체 행수(19972)가 출력되게 됩니다.

SELECT COUNT(MiddleName) FROM Person.Contact

 

경고: 집계 또는 다른 SET 작업에 의해 Null 값이 제거되었습니 다.

쿼리편집기에서 컬럼을 지정하고 사용한 COUNT 함수의 출력결과 메시지를 확인해보면 위와 같이 NULL 값이 제거된 결 과가 출력된다는 메시지가 나오게 됩니다.

COUNT 함수를 특정 조건에 해당하는 결과로 제한해서 출력을 하고자 한다면 WHERE 절을 이용해서 조건을 지정하면 됩니다.

SELECT COUNT(*) AS '9월주문' FROM Orders WHERE OrderDate Between '2017-09-01' AND '2017-09-30'

 

위의 예는 WHERE 절을 이용해서 2017년 9월에 몇건의 주문이 있었는 지를 출력해주는 쿼리문입니다.

 

SUM, AVG 함수의 사용

SUM 함수를 사용하면 합계를 출력할 수 있습니다. AVG 함수는 평균을 출력해주는 함수입니다.

SELECT SUM(QuotedPrice) AS 주문합계 FROM Order_Details WHERE ProductNumber='1'

 

위의 예는 품목코드가 1번인 주문의 합계를 출력해주는 쿼리문입니다.

SELECT SUM(SalePrice * Quantity) AS Total FROM Products

상품의 단가와 수량이 들어 있는 테이블에서 합계를 내고자 한다면 먼저 단가와 수량을 곱한 후에 SUM 함수를 적용하면 됩니다.

SELECT AVG(QuotedPrice) AS 주문평균 FROM Order_Details WHERE ProductNumber='1' SELECT AVG(SalePrice * Quantity) AS 토탈평균 FROM Products

 

AVG 함수는 SUM 함수의 사용법과 같이 함수명만 바꾸어서 사용하면 똑같은 방법으로 사용할 수 있습니다.

 

MAX, MIN 함수의 사용

 

MAX 함수를 이용하면 표현식에 의해 리턴된 최대값을 알아낼 수 있습니다. MIN 함수는 역시 표현식에 의해 리턴된 최소 값을 알아낼 수 있습니다.

SUM 함수와 AVG 함수에서는 아무런 언급이 없었던 표현식이라는 말을 사용한 이유는 MIN 과 MAX 함수는 숫자로 계 산되지 않는 값들도 함수로 사용할 수 있기 때문입니다. SUM과 AVG 함수는 숫자로 계산될 수 없는 값들에서는 함수를 사용할 수 없지만 MIN과 MAX 함수는 그런 제한을 받지 않고 함수를 사용하는 것이 가능합니다.

SELECT SUM(City) FROM Customers sum 

SELECT MAX(City) FROM Customers 

 

 충북 고객들이 사는 지역(City) 컬럼을 대상으로 SUM 함수를 사용하면 위와 같이 에러메시지가 나오지만, MAX 함수를 사용 하면 문자열에 해당하는 적절한 정렬방법에 따라 가장 마지막에 해당하는 문자를 MAX 함수가 찾아서 반환해 주게 됩니 다.

SELECT MAX(QuotedPrice) AS 최대값 FROM Order_Details SELECT MIN(QuotedPrice) AS 최소값 FROM Order_Details

 

물론 위와 같이 숫자가 들어있는 컬럼을 대상으로 MAX 함수를 이용해서 최대값, MIN 함수를 이용해서 최소값을 알아낼 수 있습니다.

SELECT MAX(Price * Quantity) AS Total FROM Order_Details WHERE OrderID = 1 SELECT MIN(Price * Quantity) AS Total FROM Order_Details WHERE OrderID = 1

 

주문수량과 단가를 곱한 후에 최대값, 최소값을 구하려면 위와 같이 쿼리문을 작성할 수 있습니다.

 

예) 주문 가격이 모든 상품의 평균가격보다 큰 상품 이름과 번호를 출력하시오. 위와 같은 조건의 데이터를 출력하려면 어떻게 하는 것이 좋을까요?

SELECT DISTINCT Products.ProductName, Products.ProductNumber FROM Products INNER JOIN Order_Details ON Products.ProductNumber = Order_Details.ProductNumber WHERE Order_Details.QuotedPrice > (SELECT AVG(RetailPrice) FROM Products)

 

위와 같은 조건의 데이터를 출력하는 효율적인 방법은 이전에 다루었던 서브쿼리를 이용하는 것이 좋습니다. 서브쿼리를 이용해서 모든 제품들의 평균을 먼저 계산한 다음 이 조건을 WHERE 절을 이용해서 데이터를 필터링해서 출력해 주는 방 법을 이용하면 쉽게 원하는 데이터를 출력할 수 있습니다.

 마지막으로 이번에는 조금 복잡한 예문을 만들어보도록 하겠습니다.

 예) 상품들 중에서 PC 완제품 상품들은 총 몇 종류의 제품들이 판매가 되었는 지 출력하시오.

SELECT COUNT(*) FROM Products WHERE Products.ProductNumber IN (SELECT Order_Details.ProductNumber FROM Order_Details INNER JOIN Products ON Order_Details.ProductNumber=Products.ProductNumber WHERE Products.ProductName LIKE 'PC%')

 

위와 같은 예제는 COUNT 함수와 IN 절 안에 서브쿼리를 사용해서 출력을 하면 원하는 데이터를 출력할 수 있습니다.

WHERE 조건절에서 제품명을 지정할 때 LIKE 'PC%' 함수를 사용하여 PC 제품만으로 한정해서 카운트하도록 역시 지정 해 줍니다.