0

So, i have the following structure of my table:

id user_agent user_ip
---------------------
18 Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.4 (KHTML, like Gecko)     Chrome/22.0.1229.94 Safari/537.4 4.3.2.1
13 Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.4 (KHTML, like Gecko) Chrome/22.0.1229.94 Safari/537.4 1.2.3.4

How i can find only unique values based on two columns(user_agent and user_ip) and count of it? I just tried following solution: 

SELECT user_agent, COUNT(*) c FROM statistics GROUP BY user_agent, user_ip HAVING c > 1;

But it won't work as i desired. Any help will be appreciated. Thanks.

getjump
  • 11
  • 4
  • 2
    select user_agent, user_ip, COUNT(*) c FROM statistics GROUP BY user_agent, user_ip HAVING c > 1; should work. If you only want unique sets that happen to have 2 or more. Is it the having condition >0 that is throwing it off? – Victor 'Chris' Cabral Nov 05 '12 at 04:57
  • I just want to make the expression, which will return user agents and count of unique user agents, based on two columns user_ip and user_agent. Now it returns only unique user agents and count of ALL of them. – getjump Nov 05 '12 at 05:11

1 Answers1

0

One possible reason might be the case, that the combination of user agent and user ip is unique in table and thus count is always 1.

Remove Having by clause, and see the result.

Zohaib
  • 7,026
  • 3
  • 26
  • 35
  • Nothing changed, still returns count of all non-unique user agents, without substraction of same user ips. – getjump Nov 05 '12 at 05:07