0

I have a table with three columns and one stores the users ip address. I want to query, but want to restrict it so that users with their ip in the ip_address column are only counted once. Here is my current query

SELECT COUNT(*) FROM articles where article_id = '1'
Improve this question
14
  • Look at the GROUP BY clause for MySQL. Also, ask programming questions on StackOverflow. Commented Feb 4, 2011 at 5:23
  • 1
    I would use GROUP BY over DISTINCT as GROUP BY allows you to specify which columns you want unique values. Although DISTINCT appears to be a function call on a column, it's not - it tries to make each row unique rather than just what you specify. Commented Feb 4, 2011 at 6:31
  • @bitxwise - You would like to use a Phillips screwdriver instead of a Torx? Distinct and Group By serve completely different purposes. OP just wants to count unique ip addresses once Commented Feb 4, 2011 at 6:34
  • @cyberkiwi: Notice that there are answers below, 1 using DISTINCT and 1 using GROUP BY. I was merely giving the OP a direction on which (I think) is the way to go. Unless I misunderstood his question =) Commented Feb 4, 2011 at 6:37
  • @bitxwise: So far I cannot see your point in trying to compare DISTINCT and GROUP BY for this particular problem. If the OP wanted a list of unique IPs, then the answers with DISTINCT and those with GROUP BY would most probably look very similar. You might say, it's because both of them modify SELECT. But of the two only DISTINCT can also be used to modify COUNT, and count is what the OP wants. With GROUP BY you would just have to resort to subselecting, an unnecessary option here, I think. Commented Feb 4, 2011 at 16:23

3 Answers 3

Reset to default
5

You want to count unique ip addresses? Use count(distinct)

SELECT COUNT(DISTINCT IP_address) FROM articles where article_id = '1'
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Just to make it clear, here is what a solution with GROUP BY might look like:

SELECT COUNT(*) FROM (SELECT IP_address FROM articles GROUP BY IP_address WHERE article_id = '1')

I only post it so others could compare it to the better solution already posted that uses COUNT(DISTINCT IP_address) and see how one is different from the other.

If there is a better way to use GROUP BY for the same task, I am yet to learn it.

Improve this answer

Comments

-2 
SELECT IP, COUNT(IP) FROM ARTICLES WHERE ARTICLE_ID = '1' GROUP BY IP
Improve this answer

3 Comments

He didn't ask for a list of how many times each IP address viewed an article, but how many times an article was viewed, only counting each IP once.
Opps, my bad... Anyways, it's answered now
Well it could be "how many times an article was viewed, only counting each IP once" or it could be "how many users viewed the article, only counting each user once as long as their IP was captured and regardless of how many IP addresses were captured for each user" - OP has yet to clarify =)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.