Nested queries to get count with two conditions

Question

I have these 3 tables,

tbl_1-
ip  |isp    |infection
----------------------
1   |aaaa   |malware
2   |bbbb   |malware
3   |cccc   |ddos
3   |cccc   |trojan
4   |dddd   |ddos

tbl_2-
ip  |isp    |infection
----------------------
1   |aaaa   |malware
3   |cccc   |ddos
4   |dddd   |trojan
5   |eeee   |trojan
6   |ffff   |other

tbl_3-
ip  |isp    |infection
----------------------
1   |aaaa   |ddos
6   |ffff   |
2   |bbbb   |other

Which i need to get the result as follows,

result-
ip  |isp    |infection  |ipCount    |ispCount   |infectionCount
--------------------------------------------------------------
1   |aaaa   |malware    |3          |3          |2
1   |aaaa   |ddos       |3          |3          |1
2   |bbbb   |other      |2          |2          |1
2   |bbbb   |malware    |2          |2          |1
3   |cccc   |ddos       |3          |3          |2
3   |cccc   |trojan     |3          |3          |1
4   |dddd   |ddos       |2          |2          |1
4   |dddd   |trojan     |2          |2          |1
5   |eeee   |trojan     |1          |1          |1
6   |ffff   |other      |2          |2          |1
6   |ffff   |           |2          |2          |1

ipCount, ispCount -> count of matching ip and isp
    eg-there are 3 records with ip = 1 and isp = aaaa

infectionCount -> count of matching infections per ip and isp
    eg-there are 2 infections that says malware where ip = 1 and isp = aaaa

I think I need a nested a query but I don't know how to count with two conditions; can you help?

EDIT : Code I tried,

SELECT ip, isp, infection, count(ip), count(isp), count(infection)
FROM (

SELECT ip, isp, infection
FROM tbl_1
UNION ALL
SELECT ip, isp, infectionType
FROM tbl_2
UNION ALL
SELECT ip, isp, infection
FROM tbl_3
)x

GROUP BY ip, isp, infection

But it doesnt give the result I desire cause I don't know how to do 2 types of counts in one query

Answer 1

You need to group column infection and (ip & ipc) differently then join them using sub-query like this:

SELECT t1.ip, t1.isp, t2.infection, t1.ipc, t1. ispc, t2.incount
FROM
    (SELECT ip, isp, infection, COUNT(ip) as ipc, COUNT(isp) as ispc
    FROM (
       SELECT ip, isp, infection
       FROM tbl1
       UNION ALL
       SELECT ip, isp, infection
       FROM tbl2
       UNION ALL
       SELECT ip, isp, infection
       FROM tbl3
       )x
     GROUP BY ip, isp) t1
JOIN
    (SELECT ip, isp, infection, COUNT(infection) as incount
     FROM (
       SELECT ip, isp, infection
       FROM tbl1
       UNION ALL
       SELECT ip, isp, infection
       FROM tbl2
       UNION ALL
       SELECT ip, isp, infection
       FROM tbl3
       )x
    GROUP BY ip, isp,  infection)t2
ON t1.ip = t2.ip
ORDER BY ip, isp, infection Desc

See this SQLFiddle

Note: I think your desired output is wrong because:

1. In Table3 there is no infection for ip=6 but it is in your output
2. infection other is missing in your output (instead there is malware)

Answer 2

You can union all the tables together then do a sum of the columns and group by the specific columns.

SELECT ip, isp, infection, COUNT(ip) AS ipcount, COUNT(isp) AS ispcount, COUNT(infection) AS infectioncount
FROM
  (
    SELECT ip, isp, infection
    FROM table_1
    UNION ALL
    SELECT ip, isp, infection
    FROM table_2
    UNION ALL
    SELECT ip, isp, infection
    FROM table_3
  )
GROUP BY ip, isp, infection
ORDER BY ip, isp, infection;