Group and filter mysql results

Question

i have a table with the following content :

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

I want to count the users of each country, and the users with IP address instead of country should be counted like "unknown";

i have managed 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 the IP addresses as an "unknown" ?
my goal is to get table like this :

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

Answer 1

If there are IP address to be considered as 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;