MySQLでAVG関数を使う際に「0」のデータを除外する方法

MySQLで平均値を計算する際、「0」のデータを除外したいケースはよくあります。例えば、テスト未受験の生徒を0点として登録している場合などです。このような場合、NULLIF()関数とAVG()関数を組み合わせることで簡単に実現できます。

NULLIF()とAVG()を組み合わせる仕組み

NULLIF(値, 0) は、第1引数の値が第2引数（ここでは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」を除外して平均値を計算する

それでは、AVG関数の使用時に「0」のエントリを除外するクエリを見てみましょう。

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

以下が出力結果です。

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

結果の検証

「0」を除外した場合、対象となるのは23、45、32の3件です。(23 + 45 + 32) ÷ 3 = 33.3333... となり、出力結果と一致します。

ちなみに、NULLIFを使わずに単純に AVG(StudentMarks) とした場合は、0も計算対象に含まれるため (23 + 0 + 45 + 0 + 32) ÷ 5 = 20 という結果になります。AdamのNULLはどちらの場合も自動的に無視される点にも注意してください。

このように、NULLIF()を活用すれば「0」を意味のあるデータとして扱いたくない場合でも、シンプルなクエリで正確な平均値を求めることができます。