CODEWITHSUNDEEP
mysqlsql
Juicy
Juicy

Reputation: 12520

Select distinct column_a and group by column_b

I have a table that holds these two columns:

ip    country

These are the viewers I've had on my site. I'm trying to see how many unique IPs I get from each country.

I came up with this:

SELECT DISTINCT ip, country, count(*) as views FROM views GROUP BY country

I was hoping this would select the number of distinct IPs and the country they are in an group them by country. I would then have a column views holding the count.

This isn't working though, this query is returning the total number of row grouped by country. I get the same result with or without the DISTINCT ip.

EDIT: I've not expressed myself clearly, what I'm trying to figure out is how many unique IPs are there from each country.

Some of my IP's are duplicate in the database, the same IP appears 3-4 times. I want to ignore that:

Input sample:

**ip**    **country**
1.1.1.1       USA
1.1.1.1       USA
1.1.1.1       USA
1.1.1.1       USA
1.1.1.2       USA
1.1.1.2       USA
1.1.1.2       USA
1.1.1.3       GER
1.1.1.3       GER
1.1.1.5       GER

should output:

USA -> 2
GER -> 2

ie: there are two different IPs from USA and two different IPs from Germany.

Upvotes: 0

Views: 45

Answers (2)

Charlesliam
Charlesliam

Reputation: 1313

Minor Changes from @Joseph B SQL

Select country, views
FROM (SELECT count(DISTINCT ip) views,
             country 
      FROM views
      GROUP BY country
      ORDER BY country);

Upvotes: 1

Joseph B
Joseph B

Reputation: 5669

Try this:

SELECT
    country,
    count(DISTINCT ip) views
FROM views
GROUP BY country
ORDER BY country;

Upvotes: 2

Related Questions