CODEWITHSUNDEEP
mysql
Zibar
Zibar

Reputation: 95

Group and filter mysql results

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 |
+-----------------+-------+

Upvotes: 5

Views: 52

Answers (3)

confusedguy
confusedguy

Reputation: 46

you can use this

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

to set a variable 'unknown' to all ip addresses

Upvotes: 0

Vladyslav Sheruda
Vladyslav Sheruda

Reputation: 1876

you can use mysql LIKE statement with if statement:

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

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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

Upvotes: 4

Related Questions