Томас Петр - 2 years ago 90
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;
``````

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.