How to get a of count of items for multiple tables

I have multiple tables (one for each day) that all have the same structure: ID, srcIP, time.

I can get the total rows for each day using the following:

SELECT
  (SELECT COUNT(*) FROM Day1) as Day1Count, 
  (SELECT COUNT(*) FROM Day2) as Day2Count,
  (SELECT COUNT(*) FROM Day3) as Day3Count

But that just gives me

Day1count  Day1count Day1count
102        148       131

But how can I get a total count for each IP address per day? Not all IPs are present every day.

srcIP       D1  D2  D3
172.16.0.1      99  34
172.16.0.2  55      22
172.16.0.3  47  49  75

Thanks in advance!

To get counts by ip and by day, the easiest way is to flatten the query:

SELECT 'day1' AS day, srcIP, count(*) AS count FROM Day1 GROUP BY srcIP
UNION
SELECT 'day2' AS day, srcIP, count(*) AS count FROM Day2 GROUP BY srcIP
UNION
SELECT 'day3' AS day, srcIP, count(*) AS count FROM Day3 GROUP BY srcIP

and then transpose it in your app to get the table format you want.

Alternatively

You can also do it by joining on IP:

SELECT srcIP, d1.count, d2.count, d3.count
FROM (SELECT srcIP, count(*) AS count FROM Day1 GROUP BY srcIP) d1
LEFT JOIN (SELECT srcIP, count(*) AS count FROM Day2 GROUP BY srcIP) d2 USING (srcIP)
LEFT JOIN (SELECT srcIP, count(*) AS count FROM Day3 GROUP BY srcIP) d3 USING (srcIP)

But here you will be missing IPs that are not in Day1, unless you first do a SELECT DISTINCT srcIP from a UNION of all days, which is pretty expensive. Basically this table structure doesn't lend itself too easily to this kind of aggregation.