MySQL AVG 함수 사용 시 0 값 제외하기: NULLIF() 활용 방법

MySQL에서 평균(AVG)을 계산할 때 0인 데이터를 제외하고 싶다면 NULLIF() 함수를 AVG() 함수와 함께 사용하면 됩니다. 핵심 원리는 간단합니다. NULLIF()가 해당 컬럼의 값이 0이면 NULL로 변환하고, AVG() 함수는 기본적으로 NULL 값을 계산에서 제외하기 때문에 자연스럽게 0이 제외된 평균을 구할 수 있습니다.

기본 문법

SELECT AVG(NULLIF(yourColumnName, 0)) AS anyAliasName FROM yourTableName;

예제 테이블 생성

먼저 실습용 테이블을 만들어 보겠습니다.

mysql> create table AverageDemo
    - > (
    - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    - > StudentName varchar(20),
    - > StudentMarks int
    - > );
Query OK, 0 rows affected (0.72 sec)

데이터 삽입

INSERT 명령을 사용해 학생별 점수 데이터를 입력합니다. 여기서는 의도적으로 NULL과 0이 섞여 있도록 구성했습니다.

mysql> insert into AverageDemo(StudentName,StudentMarks) values('Adam',NULL);
Query OK, 1 row affected (0.12 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('Larry',23);
Query OK, 1 row affected (0.19 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('Mike',0);
Query OK, 1 row affected (0.20 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('Sam',45);
Query OK, 1 row affected (0.18 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('Bob',0);
Query OK, 1 row affected (0.12 sec)
mysql> insert into AverageDemo(StudentName,StudentMarks) values('David',32);
Query OK, 1 row affected (0.18 sec)

전체 데이터 조회

SELECT 문으로 저장된 모든 레코드를 확인해 보겠습니다.

mysql> select *from AverageDemo;

실행 결과는 다음과 같습니다.

+----+-------------+--------------+
| Id | StudentName | StudentMarks |
+----+-------------+--------------+
|  1 | Adam        |         NULL |
|  2 | Larry       |           23 |
|  3 | Mike        |            0 |
|  4 | Sam         |           45 |
|  5 | Bob         |            0 |
|  6 | David       |           32 |
+----+-------------+--------------+
6 rows in set (0.00 sec)

0을 제외한 평균 계산하기

이제 NULLIF()를 적용하여 점수가 0인 학생(Mike, Bob)을 평균 계산에서 제외해 보겠습니다.

mysql> select AVG(nullif(StudentMarks, 0)) AS Exclude0Avg from AverageDemo;

실행 결과는 다음과 같습니다.

+-------------+
| Exclude0Avg |
+-------------+
| 33.3333     |
+-------------+
1 row in set (0.05 sec)

결과 분석

결과값은 33.3333입니다. 이는 23, 45, 32 세 개의 값에 대한 평균입니다. 참고로 0을 제외하지 않고 단순히 AVG(StudentMarks)를 실행하면 (23 + 0 + 45 + 0 + 32) ÷ 5 = 20이 되어 실제 성능보다 낮은 왜곡된 평균이 나오게 됩니다. 따라서 설문 조사, 센서 데이터, 성적 처리처럼 0이 '실제 값'이 아닌 경우에는 NULLIF()를 활용한 이 방식이 정확한 통계를 얻는 데 매우 유용합니다.