トーハム紀行

先日のSQLでクロス集計した際、助言をいただけて別の方法で解決した（Dupont氏に感謝）。

俺なりに何が違うのか考えてみた結果、

sum()とcount()で結果が違う
coalesce()関数とは

の2点に絞って調査してみた。

まず前回俺が書いたSQLは以下になる。

select name as '商品名',
 count(case when color='白' then 1 else null end) as '白',
 count(case when color='黒' then 1 else null end) as '黒',
 count(case when color='赤' then 1 else null end) as '赤',
 count(case when color='青' then 1 else null end) as '青',
 count(case when color='緑' then 1 else null end) as '緑',
 case
  when name='ビックリ弁当箱' then sum(field(name, 'ビックリ弁当箱'))
  when name='最果てグローブ' then sum(field(name, '最果てグローブ'))
  when name='無駄毛ロンガー' then sum(field(name, '無駄毛ロンガー'))
  else null
 end as '合計'
from history
group by name with rollup

そしてDupont氏の助言は以下。

SELECT COALESCE(`name`, '合計') AS `商品名`,
SUM(CASE WHEN `color`='白' THEN 1 ELSE 0 END) AS '白',
SUM(CASE WHEN `color`='黒' THEN 1 ELSE 0 END) AS '黒',
SUM(CASE WHEN `color`='赤' THEN 1 ELSE 0 END) AS '赤',
SUM(CASE WHEN `color`='青' THEN 1 ELSE 0 END) AS '青',
SUM(CASE WHEN `color`='緑' THEN 1 ELSE 0 END) AS '緑',
COUNT(*) AS `合計`
FROM `history`
GROUP BY `name` WITH ROLLUP;

※COALESCE()関数とWITH ROLLUPは条件が一緒になるように追記してる

大文字小文字は抜きにして、基本的にcount()がsum()になっている点と、小計がcase式か、count()か、という点が大きく違う。