How to get daily counts from two tables in mysql?-CodePudding

I have a following problem. I have two tables:

TableA:

table_a_datetime | id_a
------------------------
2012-03-06 10:55:30 | 1
2012-03-06 18:55:30 | 5
2012-03-07 10:55:30 | 6

TableB:

table_b_datetime | id_b
------------------------
2012-03-06 11:55:30 | 10
2012-03-07 12:55:30 | 51
2012-03-07 19:55:30 | 62

I would like to get following result:

date | count(id_a) | count(id_b)
------------------------
2012-03-06 | 2 | 1
2012-03-07 | 1 | 2

I tried this, but it doesn`t work:

SELECT 
  date(table_a_datetime), 
  COUNT(id_a), 
  date(table_b_datetime), 
  COUNT(id_b)
FROM TableA
JOIN TableB 
  ON date(TableA.table_a_datetime) = date(TableB.table_b_datetime)                              
GROUP BY date(TableA.table_a_datetime)

How can I get the desired output, please?

CodePudding user response：

You won't be able to do this with a JOIN, as you may have dates in TableA not present in TableB (or vice versa).

You can use an UNION to merge both tables:

SELECT date, COUNT(id_a), COUNT(id_b)
FROM (
  SELECT date(table_a_datetime) AS date, id_a, NULL AS id_b 
    FROM TableA
  UNION
  SELECT date(table_b_datetime) AS date, NULL AS id_a, id_b
    FROM TableB
) AS merge
GROUP BY date

-- date        COUNT(id_a)  COUNT(id_b)
-- 2012-03-06  2             1
-- 2012-03-07  1             2

CodePudding user response：

You can use OUTER JOIN . But you have to use two OUTER JOIN to simulate a full union. Try this:

insert into TableA values('2012-03-08 20:33:20', 28); -- unique date for A
insert into TableB values('2012-03-09 20:33:20', 33); -- unique date for B
select date_a as date , `count(id_a)`, `count(id_b)` from 
    ( select date_a,count(id_a) as `count(id_a)` from (select date(table_a_datetime) date_a ,id_a from TableA) t1 group by date_a) ta
    left join
    (select date_b,count(id_b) as `count(id_b)` from (select date(table_b_datetime) date_b ,id_b from TableB) t2 group by date_b) tb
    on ta.date_a=tb.date_b
union  
select date_b as date , `count(id_a)`, `count(id_b)` from 
    (select date_b,count(id_b) as `count(id_b)` from (select date(table_b_datetime) date_b ,id_b from TableB) t2 group by date_b) tb
    left  join
    ( select date_a,count(id_a) as `count(id_a)` from (select date(table_a_datetime) date_a ,id_a from TableA) t1 group by date_a) ta
    on ta.date_a=tb.date_b;