Left Join Count

Question

Ok i think i am going about this the wrong way possibly, but i have tried so many different ideas and cannot seem to find the right way to do this, so hoped somebody might be able to nudge me in the right direction.

Basically i record the ip address of visitors as they visit my website, and then identify which of the ip addresses belong to businesses.

So table one has a long list of ip addresses of people who have visited my website and table two has a list of ip addresses i have identified.

Now what i am trying to do is find out how many of the IP addresses in Table 1 have been identified.

The code i have below matches and groups the ip addresses and shows me how many times the ip address in table 2 appears in table one.

However what i am trying to get is just one number and not a group of numbers, i have tried writing the query several ways but can only get it to work with a group

SELECT ip_stats.ip, COUNT(ip_stats.ip) 
FROM ip_stats 
LEFT JOIN main USING (ip)
GROUP BY ip_stats.ip

ip_stats is table one and has the long list of people visiting my website. main is table two and contains the details of the IP addresses which have been identified.

both tables have their own primary key both are which are id.

Thanks

Answer 1

COUNT the ip on main table

SELECT ip_stats.ip, COUNT(`main`.ip)
FROM ip_stats 
LEFT JOIN main USING (ip)
GROUP BY ip_stats.ip 

UPDATE 1

ah ok, so you only want one value for all ip that are present on main table right? then you don't need to use LEFT JOIN for this, instead use INNER JOIN

SELECT COUNT(DISTINCT ip_stats.ip)
FROM ip_stats 
     INNER JOIN main USING (ip) 

DISTINCT was added so only unique values are being counted.