Mysql Join query with count table records with same date

Question

Hello i am having two different table with same field created_date (datetime) now i want records which counts daywise records with joining table i have done for individual counting as below query :

SELECT DATE(created_date), COUNT(*) FROM table1 GROUP BY DAY(created_date)
SELECT DATE(created_date), COUNT(*) FROM table2 GROUP BY DAY(created_date)

and i am getting results for individuals something like this:

RESULT I NEED :

DATE(created_date)      count(table1)       count(table2)
2016-12-01                  10                  3
2016-12-02                  1                   0
2016-12-05                  1                   0
2016-11-29                  1                   0
2016-11-30                  4                   1

Now i just want to join these result WITH INDIVIDUAL VIEW COUNT ACCORDING TO TABLE can anyone please help me out with this profile....

Answer 1

First take a UNION between your two tables, then use conditional aggregation to determine the counts for each of the two tables. Note that I introduce a field called table_name to keep track of data from each of the two tables.

SELECT t.created_date,
       SUM(CASE WHEN t.table_name = 'one' THEN 1 ELSE 0 END) AS count_table_one,
       SUM(CASE WHEN t.table_name = 'two' THEN 1 ELSE 0 END) AS count_table_two
FROM
(
    SELECT DATE(created_date) AS created_date, 'one' AS table_name
    FROM table1
    UNION ALL
    SELECT DATE(created_date), 'two'
    FROM table2
) t
GROUP BY t.created_date

I used DATE consistently everywhere to make the query correct.