[MSSQL]집계함수(Aggregate Function)를 이용하여 특정 값(0...)을 제외, NULL을 포함하고 집계하기

서론

 

데이터를 집계할 때 특정 데이터를 제외하고 집계를 해야 할 경우가 있습니다. 특히 AVG를 사용한 평균 집계인 경우 0을 제외하거나 NULL을 포함하여 평균을 구해달라는 요구사항을 종종 요청받을 때가 있습니다. 오늘은 이에 대한 내용을 포스팅하겠습니다.

 

---

 

본론

 

집계함수는 NULL 데이터를 제외하고 집계를 수행합니다. 따라서 아래 예시와 같이 CASE문을 사용하여 제외시킬 데이터를 NULL로 변환시키면 이를 제외하고 집계를 수행하게 됩니다.

 

[예제]

 

--테스트할 임시테이블 및 데이터 생성
SELECT 'ALICE' [NAME], 100 [SCORE]
INTO #TEMP_TABLE
UNION ALL
SELECT 'BOB' , 0 
UNION ALL
SELECT 'SAM' , 30 
UNION ALL
SELECT 'TOM' , 80 
UNION ALL
SELECT 'SWAN' , 0 
UNION ALL
SELECT 'WILLIAM' , 10 
UNION ALL
SELECT 'KEVIN' , 70 
UNION ALL
SELECT 'ARIANNA' , 80

 

[조회 결과]

 

 

1) 0을 제외한 평균 집계

 

SELECT 
		 AVG(SCORE) SCORE_AVG1--전체 평균 집계
		,AVG(CASE WHEN SCORE = 0 THEN NULL ELSE SCORE END) SCORE_AVG2--0을 제외한 평균 집계
FROM #TEMP_TABLE

 

[실행 결과]

 

 

2) 특정 범위를 제외한 평균 집계 (예시는 0 이상 30 이하)

 

SELECT AVG(CASE WHEN SCORE BETWEEN 0 AND 30 THEN NULL ELSE SCORE END) SCORE_AVG 
FROM #TEMP_TABLE

 

[실행 결과]

 

 

반대로 NULL 데이터도 0으로 취급하여 집계에 포함시키고 싶을 경우 ISNULL 내장 함수를 이용하여 NULL을 0으로 변환하면 원하는 집계 결과를 구할 수 있습니다.

 

--테스트할 임시테이블 및 데이터 생성(NULL 포함) 
SELECT 'ALICE' [NAME], 100 [SCORE]
INTO #TEMP_TABLE
UNION ALL
SELECT 'BOB' , 0 
UNION ALL
SELECT 'SAM' , 30 
UNION ALL
SELECT 'TOM' , 80 
UNION ALL
SELECT 'SWAN' , NULL 
UNION ALL
SELECT 'WILLIAM' , 10 
UNION ALL
SELECT 'KEVIN' , NULL 
UNION ALL
SELECT 'ARIANNA' , 80

 

[조회 결과]

 

 

3) NULL 데이터를 포함한 평균 집계

 

SELECT 
		 AVG(SCORE) SCORE_AVG1--전체 평균 집계(NULL 제외)
		,AVG(ISNULL(SCORE,0)) SCORE_AVG2--NULL을 포함한 평균 집계(NULL -> 0 변환)
FROM #TEMP_TABLE

 

[실행 결과]

 

 

---

 

결론

 

집계 함수가 NULL을 제외하고 집계를 수행하는 특성을 이용하여 여러 예제를 구현해보았습니다. 이는 예제에서 사용한 AVG 집계 함수뿐만 아니라 SUM, MIN, MAX 등 모든 집계 함수에 해당하는 내용이므로 이번 글에서 알아본 특성을 이해하고 원하는 집계 결과를 조회하는데 도움이 되는 글이 되었으면 좋겠습니다.