SQLの集計関数で NULLを含む列を集計する場合の注意点

MySQLとPostgreSQLの話。他のRDBMSは試してない。


SQLでNULLを含む列を集計する場合、NULLの行は無視される。

SELECT
  SUM(t.v) -- => 15
, MIN(t.v) -- => 0
, MAX(t.v) -- => 10
, COUNT(t.v) -- => 3 (NULLの行も入れれば4行)
, AVG(t.v) -- => 5.0000 (NULLの行をゼロと考えれば3.75)
FROM (
  SELECT NULL v
  UNION
  SELECT 0 v
  UNION
  SELECT 5 v
  UNION
  SELECT 10 v
) t

（PostgreSQL 8.4とMySQL 5.1で確認。）


SUM()やMIN()、MAX()では問題ないが、COUNT()やAVG()ではNULLの行の分も含めて計算したい場合に困る。

そういう場合、COALESCE()でNULLをゼロに変換するとよい。

SELECT
  COUNT(COALESCE(t.v, 0)) -- => 4
, AVG(COALESCE(t.v, 0)) -- => 3.7500
FROM (
  SELECT NULL v
  UNION
  SELECT 0 v
  UNION
  SELECT 5 v
  UNION
  SELECT 10 v
) t

MySQLの場合はIFNULL()でもOK。


参考
PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Conditional Expressions
MySQL :: MySQL 5.5 Reference Manual :: 11.2.3 Comparison Functions and Operators