Mysql group and filtering results

Question

I have a table with the following contents:

  |  Country |  Username |
 + --------------- + ---------------- +
 |  US |  John |
 |  IT |  Pedro |
 |  US |  Bob |
 |  FR |  Jill |
 |  192.168.1.2 |  Roupraht |
 |  192.168.1.20 |  Antonio |
 + --------------- + ---------------- +

I want to calculate the users of each country, and users with an IP address instead of a country should be considered "unknown";

I was able to write the following SQL query:

select country, count(*) as total from users group by country; 

And I got the following result:

  + ----------------- + ------- +
 |  country |  total |
 + ----------------- + ------- +
 |  192.168.1.2 |  1 |
 |  192.168.1.20 |  1 |
 |  US |  2 |
 |  IT |  1 |
 |  FR |  1 |
 + ----------------- + ------- +

How can I count all IP addresses as "unknown"?
my goal is to get the table as follows:

  + ----------------- + ------- +
 |  country |  total |
 + ----------------- + ------- +
 |  Unknown |  2 |
 |  US |  2 |
 |  IT |  1 |
 |  FR |  1 |
 + ----------------- + ------- +

Answer 1

If the IP address is considered Unknown , you can do the following trick

 select country,count(*) as tot from users where inet_aton(country) is null group by country union all select 'Unknown',count(*) as tot from users where inet_aton(country) is not null; 

https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton

Answer 2

you can use mysql LIKE statement with if statement:

 select if(country LIKE '%.%.%.%', 'unknown', country), count(*) as total from users group by country; 

Answer 3

you can use this

 set @unknown = (select country from table where country LIKE '%.%.%.%'); 

set the variable 'unknown' to all IP addresses