SELECT date(date) as day, count(IF(name = 'red', 1, NULL)) AS "red",
count(IF(name = 'blue', 1, NULL)) AS "blue",
count(IF(name = 'yellow', 1, NULL)) AS "yellow" FROM test1 group by day with rollup

select date(date) as day, count(*) as total from test2 group by day with rollup