Mysql - Join two tables after group by

Question

I have two tables in the following structure:

Table - a
datetime            | camapgin_id | country | unique_id
2013-12-23 10:23:59 |     123     |    US   |  5475642385763


Table - b
datetime            | camapgin_id | country | unique_id
2013-12-23 10:40:35 |     123     |    US   |  5475642385763

I would like to have a query to generate a table like the following:

Table - joined
day        | camapgin_id | country | count(1) from a | count(1) from b
2013-12-23 |     123     |    US   |       100       |      82

I can generate each table with its own query like this:

SELECT date_format(datetime, '%Y-%m-%d') AS day, campaign_id, country, count(1)
FROM a
GROUP BY day, campaign_id, country

And the same for table b, but this gets me to two distinct tables.

How can i join the two tables by the same group by as in the query i posted?

Answer 1

Try this:

SELECT DATE_FORMAT(A.day, '%Y-%m-%d') AS day, A.campaign_id, A.country, MAX(tableACount), MAX(tableBCount)
FROM (SELECT DATE(datetime) AS day, campaign_id, country, COUNT(1) tableACount
      FROM a GROUP BY day, campaign_id, country
     ) AS A
INNER JOIN (SELECT DATE(datetime) AS day, campaign_id, country, COUNT(1) tableBCount
            FROM b GROUP BY day, campaign_id, country
           ) AS B ON A.day = B.day AND A.campaign_id = B.campaign_id AND A.country = B.country
GROUP BY A.day, A.campaign_id, A.country

EDIT::

SELECT DATE_FORMAT(A.day, '%Y-%m-%d') AS DAY, A.campaign_id, A.country, MAX(tableACount), MAX(tableBCount)
FROM (SELECT DATE(DATETIME) AS DAY, campaign_id, country, COUNT(1) tableACount, 0 AS tableBCount
      FROM a GROUP BY DAY, campaign_id, country
      UNION 
      SELECT DATE(DATETIME) AS DAY, campaign_id, country, 0 AS tableACount, COUNT(1) tableBCount
      FROM b GROUP BY DAY, campaign_id, country
     ) AS A
GROUP BY A.day, A.campaign_id, A.country