Select floor(deweyClass/100)*100 as dewey, 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 'orange %' or title like '% orange %' or title like '% orange' THEN 1 ELSE 0 END) as orange, 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 'green %' or title like '% green %' or title like '% green' THEN 1 ELSE 0 END) as green, 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 'indigo %' or title like '% indigo %' or title like '% indigo' THEN 1 ELSE 0 END) as indigo, SUM(CASE WHEN title like 'violet %' or title like '% violet %' or title like '% violet' THEN 1 ELSE 0 END) as violet from inraw where deweyClass < 1000 and cout >= '2011-01-01' and cout <= '2011-01-31' group by floor(deweyClass/100)*100 with rollup
000^^20^^40^^70^^22^^151^^79^^0
100^^47^^0^^0^^2^^51^^9^^0
200^^11^^0^^0^^3^^10^^0^^0
300^^181^^32^^1^^166^^99^^0^^5
400^^5^^0^^0^^0^^0^^0^^0
500^^22^^7^^8^^12^^120^^0^^0
600^^47^^15^^16^^358^^88^^3^^0
700^^404^^59^^106^^402^^723^^30^^99
800^^50^^0^^13^^9^^34^^2^^1
900^^72^^3^^10^^26^^37^^0^^4
null^^843^^116^^154^^978^^1162^^44^^109
