Select floor(deweyClass/100)*100 as dewey, SUM(CASE WHEN title like 'violet %' or title like '% violet %' or title like '% violet' THEN 1 ELSE 0 END) as violet, SUM(CASE WHEN title like 'indigo %' or title like '% indigo %' or title like '% indigo' THEN 1 ELSE 0 END) as indigo, SUM(CASE WHEN title like 'blue %' or title like '% blue %' or title like '% blue' THEN 1 ELSE 0 END) as blue, SUM(CASE WHEN title like 'green %' or title like '% green %' or title like '% green' THEN 1 ELSE 0 END) as green, SUM(CASE WHEN title like 'yellow %' or title like '% yellow %' or title like '% yellow' THEN 1 ELSE 0 END) as yellow, SUM(CASE WHEN title like 'orange %' or title like '% orange %' or title like '% orange' THEN 1 ELSE 0 END) as orange, SUM(CASE WHEN title like 'red %' or title like '% red %' or title like '% red' THEN 1 ELSE 0 END) as red, SUM(CASE WHEN title like 'black %' or title like '% black %' or title like '% black' THEN 1 ELSE 0 END) as black, SUM(CASE WHEN title like 'white %' or title like '% white %' or title like '% white' THEN 1 ELSE 0 END) as white, SUM(CASE WHEN title like 'violet %' or title like '% violet %' or title like '% violet' or 'indigo %' or title like '% indigo %' or title like '% indigo' or 'blue %' or title like '% blue %' or title like '% blue' or 'green %' or title like '% green %' or title like '% green' or 'yellow %' or title like '% yellow %' or title like '% yellow' or 'orange %' or title like '% orange %' or title like '% orange' or 'red %' or title like '% red %' or title like '% red' or 'black %' or title like '% black %' or title like '% black' or 'white %' or title like '% white %' or title like '% white' THEN 1 ELSE 0 END) as total from inraw where deweyClass < 1000 and cout >= '2011-01-01' and cout <= '2011-01-31' group by floor(deweyClass/100)*100 with rollup
109^^44^^1162^^978^^154^^116^^843^^2397^^1314^^4119
