Merging 2 Table and GROUP BY date

Question

I need to merge multiple table group by the count base on date's day.

Below are my table structure :

#table1
id date
1  2015-07-01 00:00:00
2  2015-07-02 00:00:00
3  2015-07-03 00:00:00

#table2
id date
1  2015-07-02 00:00:00
2  2015-07-02 00:00:00
3  2015-07-02 00:00:00
4  2015-07-10 00:00:00

What I wanted to achieve :

#query result
date       t1_count   t2_count
2015-07-01 1          NULL
2015-07-02 1          3
2015-07-03 1          NULL
2015-07-10 NULL       1

Below are my query that refer to this link:

SELECT left(A.date,10) AS `day`
     , COUNT(A.ID) AS `a_count`
     , COUNT(B.ID) AS `b_count`
  FROM table1 A

 LEFT JOIN table2 B
    ON LEFT(A.date,10) = LEFT(B.date,10)
 GROUP BY LEFT(A.date,10)

UNION

SELECT left(B.date,10) AS `day`
     , COUNT(A.ID) AS `a_count`
     , COUNT(B.ID) AS `b_count`
  FROM table1 A

 RIGHT JOIN table2 B
    ON LEFT(A.date,10) = LEFT(B.date,10)

 GROUP BY LEFT(A.date,10);

but the result was

#query result
date       t1_count   t2_count
2015-07-01 1          0
2015-07-02 3          3
2015-07-03 1          0
2015-07-10 0          1

I'd try to modified and search other solution like UNION ALL, LEFT JOIN, etc, but I'd no luck to solve this problem.

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-07-18T17:58:33

You can do this using union all and group by:

select date, sum(istable1) as numtable1, sum(istable2) as numtable2
from ((select date(date) as date, 1 as istable1, NULL as istable2
       from table1
      ) union all
      (select date(date) as date, NULL as istable1, 1 as istable2
       from table2
      )
     ) t
group by date
order by 1;

Under some circumstances, it can be faster to aggregate the data in the subqueries as well:

select date, sum(numtable1) as numtable1, sum(numtable2) as numtable2
from ((select date(date) as date, count(*) as numtable1, NULL as numtable2
       from table1
       group by date(date)
      ) union all
      (select date(date) as date, NULL as numtable1, count(*) as numtable2
       from table2
       group by date(date)
      )
     ) t
group by date
order by 1;

If you want 0 instead of NULL in the desired results, use 0 instead of NULL in the subqueries.

Merging 2 Table and GROUP BY date

1 Answers