📝
SQLでNULLを0や他の値としてAVGやSUMを計算したい
結論
IFNULL()かCOALESCE()を使います。
説明
次のようなtest_scoreテーブルがあったとします。
| id | score1 |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | NULL |
「score1の平均を取りたい」時、単純にscore1に対してAVG()関数を使うと、結果はNULLであるid=4のユーザを除いた平均値である 20 となります。
SELECT AVG(score1) FROM test_score; # 結果は20
ただし、要件によっては「NULLのユーザを0として平均値を出したい」ということがあると思います。
その場合、以下のようにすると期待する結果を得られます。
# 方法1
SELECT AVG(IFNULL(score1, 0)) FROM test_score; # 結果は15
# 方法2
SELECT AVG(COALESCE(score1, 0)) FROM test_score; # 結果は15
ここで、COALESCE()は「与えられた引数のうち、NULLでない最初の引数を返す関数」です。
したがって、例えば「試験が受けられなかったユーザ（score1がNULL）には再試験を受けさせ、その結果をscore2のカラムに格納する。再試験を受けた者は再試験の点数をその人の点数として全体の平均値を出したい」といった要件の時に便利です。
| id | score1 | score2 |
|---|---|---|
| 1 | 10 | NULL |
| 2 | 20 | NULL |
| 3 | 30 | NULL |
| 4 | NULL | 20 |
SELECT AVG(COALESCE(score1, score2, 0)) FROM test_score; # 結果は20
上記はAVGだけではなく、SUMに関しても同様のことが言えます。
MySQLのリファレンス
Discussion