MSSQL 특정한 값 제외 평균

개요

Invalid한 값 0을 제외한 값들로 평균을 내고 싶어졌다.

그럴땐 IFNULL을 사용하면 된다.

 

본론

-- 테스트 테이블 세팅
CREATE TABLE Test1 
(
    uid		BIGINT,
    score	BIGINT,
    CONSTRAINT PK_TEST1 PRIMARY KEY (uid)
)
GO
 
INSERT INTO dbo.Test1 VALUES (1, 1)
INSERT INTO dbo.Test1 VALUES (2, 5)
INSERT INTO dbo.Test1 VALUES (3, 3)
INSERT INTO dbo.Test1 VALUES (4, 0)
INSERT INTO dbo.Test1 VALUES (5, 0)
INSERT INTO dbo.Test1 VALUES (6, 0)
INSERT INTO dbo.Test1 VALUES (7, 0)
INSERT INTO dbo.Test1 VALUES (8, 4)
INSERT INTO dbo.Test1 VALUES (9, 5)
INSERT INTO dbo.Test1 VALUES (10, 7)

위와 같이 테스트 데이터들을 넣고 평균을 내면 아래와 같이 나온다.

 

 

SELECT AVG(score) AS AVERAGE FROM dbo.Test1;

 

여기서 0을 제외한 값으로 평균을 내고 싶다면 아래와 같이 쓰면 된다.

 

 

 

SELECT AVG(NULLIF(score,0)) AS AVERAGE FROM dbo.Test1;

NULLIF(score, 0)는 score가 0이면 NULL로 간주하겠다는 뜻이다.

AVG 함수는 NULL 값을 무시하므로 0을 무시하게 되는 것이다.

하지만 AVG 함수에 NULL만 있다면 NULL을 리턴하게 되므로 아래와 같이 하는게 제일 좋겠다.

 

 

 

SELECT ISNULL(AVG(NULLIF(score,0)), 0) AS AVERAGE FROM dbo.Test1;

 

이렇게 하면 AVG 함수의 최종 경과가 NULL일 경우 0을 반환하게 된다.

MAX, MIN, SUM과 같은 다른 산술 함수도 같은 방식으로 쓸 수 있다.