complex mysql query count from 2 table and group by

Question

i have 2 tables inbound,outbound two tables have same structure

inbound

id | site_id | ip (bigint) | date
 1 | 1       | 12345678890 | 20140123
 2 | 1       | 12341234000 | 20140123

outbound

id | site_id | ip          | date
 1 | 1       | 12345678890 | 20140123
 2 | 1       | 12341234000 | 20140124
 3 | 1       | 12341234000 | 20140124

my input is only site_id i want to combine inbound and outbound tables and get result as

inbound_unique_ip_count |  outbound_unique_ip_count | date
                      2 |                         1 | 20140123
              0 or null |                         1 | 20140124

i think group by date should work?

Answer 1

Use this:

select date, COUNT(distinct(a.inbound_unique_ip_count) ) as `inbound_unique_ip_count`, 
COUNT(distinct (outbound_unique_ip_count )) as `outbound_unique_ip_count` 
from inbound a JOIN outbound b on a.site_id=b.site_id 
group by 1 

Answer 2

SELECT COUNT(distinct i.ip) AS inbound_unique_ip_count,
       COUNT(distinct o.ip) AS outbound_unique_ip_count,
       date
FROM inbound AS i
JOIN outbound AS o USING (site_id, date)
WHERE i.site_id = :site_id
GROUP BY date

This will only work properly if both tables have all the dates and the input site_id. If either table can be missing some of these, you need a FULL OUTER JOIN, which MySQL doesn't support. If you search SO for this, you should be able to find workarounds, I leave that as an exercise for the reader.