Selecting rollup after join

Question

Moved from UX post

I have 2 queries with roll up in each one..

---

  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

---

When joining them the rollup row gets removed so I solved it by using another query calculating the rollup and union it to the end of the result

Here's a live demo [sqlfiddle], any thoughts about the way I'm doing this (like best practice or performance), if there's a way to keep the rollup after join, that I don't know of. Any pointers appreciated.

update

the correct result set I want is the result in the fiddle. each row contains a summary of one day, from the 1st query number of items/colors and from the 2nd query a number of requests on that day. the rollup would be the categorized total of each item/color and requests. hope I made it clear :)

Answer 1

I don't think the "rollup" gets removed. The resulting rows simply don't match the join conditions. You could try this:

select t1.*, t2.total
from (SELECT date(date) as day, count(IF(name = 'red', 1, NULL)) AS "red",
             sum(name = 'blue') AS blue,
             sum(name = 'yellow') AS yellow
      FROM test1
      group by day with rollup 
     ) t1 join 
     (select date(date) as day, count(*) as total
      from test2
      group by day with rollup
     ) t2
     on t1.day = t2.day or t1.day is null and t2.day is null;