Томас Петр Томас Петр - 2 months ago 7
MySQL Question

SQL: Count with group by problems

I have problem with my script - what I need?

I have this table :

--------------------------------
| id | ip | country |
--------------------------------
| 1 | 80.100.100.20 | CZ |
--------------------------------
| 2 | 80.100.100.20 | CZ |
--------------------------------
| 3 | 90.900.900.90 | CZ |
--------------------------------
| 4 | 55.555.555.55 | RU |
--------------------------------


This is my table with "visitors" so I need get count of visitors from countries. In this case I need:

2 CZ
1 RU


Rows with country = CZ is 3, but 2 have same IP address, so real visitors are 2.

How can i do it? Thanks.

I'm trying some like this:

SELECT count(DISTINCT `ip`) AS `count`, `country`
FROM `1799_visitors`
GROUP BY `ip`


Returns:

1 CZ
1 CZ
1 RU


It's probably good, but I need sum of them.

2 CZ
1 RU


It's probably solved by this solution:

SELECT count(DISTINCT `ip`) AS `count`, `country`
FROM `1799_visitors`
GROUP BY `country`


But in another way, I need also count of all counts, like this:

SELECT count(DISTINCT `ip`) AS `count`, `country`
FROM `1799_visitors`
GROUP BY `country`


Results:

CZ 2
RU 1


I need visitors = 3, i'm thinking about this, but I do not want use 2 select, do you have another solution for me?

SELECT SUM(count) FROM (SELECT COUNT( DISTINCT `ip` ) AS `count` , `country`
FROM `1799_visitors`
GROUP BY `country`
LIMIT 0 , 100) src;

Answer

You need to GROUP BY only by country:

SELECT country, COUNT(DISTINCT ip) AS Ipcount FROM 1799_visitors GROUP BY country;

GROUP BY ip means that the COUNT() function is called once for each ip and thus will return 1 on very call. GROUP BY country calls the function COUNT() with all the ip-addresses for each country. Adding DISTINCT means that only the number of unique ip-addresses is counted, which is the result you want.