Select from two tables with group by date

Question

I have two tables:

Table t1:

id | date_click
 1 | 2016-02-31 17:17:23
 2 | 2016-03-31 12:11:21
 3 | 2016-03-31 13:13:23

So from this table I want to get count field Id for each day.

For this I use next query:

SELECT date_format(date_click, '%Y-%m-%d') as date_click_event
     , COUNT(id) as count_click 
  FROM t1 
 GROUP 
    BY date_click_event 
 ORDER 
    BY date_click_event DESC;

It's work good.

So next table is t2.

id | count | date_sent
 1 |    33 | 2016-02-31 11:12:23
 2 |    22 | 2016-03-31 14:11:22
 3 |    11 | 2016-03-31 13:12:13

To select data by date from this table I use next query:

SELECT date_format(date_sent, '%Y-%m-%d') as date_sent_push
     , SUM(count) as count_sent 
  FROM t2 
 GROUP 
    BY date_sent_push 
 ORDER 
    BY date_sent_push DESC 
 LIMIT 100;

It's also work good. So my purpose is merge these two queries into one SELECT that next I can write in php one tables with count of Id by date from table t1 and with count of count field from table t2 by date.

When I try next query:

SELECT date_format(t2.date_sent, '%Y-%m-%d') AS date_sent_push
     , SUM(t2.count) as count_sent
     , COUNT(t1.id) as count_click 
  FROM t2
     , t1 
 WHERE date_format(t2.date_sent, '%Y-%m-%d') = date_format(t1.date_click, '%Y-%m-%d') 
 GROUP 
    BY date_sent_push 
 ORDER 
    BY date_sent_push 
  DESC 
 LIMIT 100;

It's not work. What I do wrong?

Answer 1

First you should UNION these results and then group by days and select aggregate fields. Also you can JOIN these queries but it can be a problem if some days miss in one of two tables:

SELECT date_sent_push, 
       MAX(count_click) as count_click,
       MAX(count_sent) as count_sent 
FROM
(SELECT date_format(date_click, '%Y-%m-%d') as date_sent_push
     , COUNT(id) as count_click
     , NULL as count_sent
  FROM t1 
  GROUP BY date_sent_push 
  UNION ALL
  SELECT date_format(date_sent, '%Y-%m-%d') as date_sent_push
     , NULL as count_click
     , SUM(count) as count_sent 
  FROM t2 
  GROUP 
    BY date_sent_push 
) as t3
GROUP BY date_sent_push

SQL fiddle demo