Need help on MySQL DISTINCT+COUNT query

Question

I have a table called "r", inside that table I store id, ip, and country. I am running this query:

SELECT  r.country, count(rr.ip), count(DISTINCT rr.ip) 
FROM `r` 
LEFT JOIN r rr 
ON r.country = rr.country 
GROUP BY r.country

I do get rows with columns: "country";"number";"number"; But both "counts()" doesn not show what I want it to show (count of raw/unique ips for each country), and I am not sure why. For example for US I see >2000 unique ips, but I only have 500 entries in the database. What is wrong with my query?

What I want to retrieve from the db is a list of row with columns: countries + count raw ips + count unique ips. (count related to each country), I mean retrieve a list of distinct countries and counting ips foreach one (without having to do multiple queries).

Answer 1

Something like (totally untested):

SELECT  
   r.country, 
   count(r.ip), 
   (SELECT COUNT(DISTINCT rr.ip) from r rr where rr.country = r.country)
FROM 
   r r
GROUP BY 
   r.country 

Answer 2

SELECT  r.country, count(r.ip), count(DISTINCT r.ip) 
FROM `r`
GROUP BY r.country

Answer 3

Why do you need a self join here? In a quick-n-dirty setup of what you have in sqlite I was able to just do

 SELECT country, count(ip), count(DISTINCT ip) FROM r GROUP BY country

...and I got the expected results.