2 Replies Latest reply on Jun 19, 2007 4:08 PM by AngryCloud

    MySQL: Counting each different IP address only once per ID

    AngryCloud Level 1
      I posted this question as a new topic to give it an appropriate name and a fresh start.

      I think this is a challenge for anyone who wants to try taking it up:

      The statement below displays each submission viewed on my site in the last 7 days, ordered by the number of views. I would like the 'id_count' to only count each unique ip address once per submission id. So if the same ip address viewed the same submission 5 times in the last week, it should only be counted as once. How can I make this work?

      Here is the MySQL statement:

      SELECT submission_views.submission_id, submission_views.timestamp, submission_views.ip_address, COUNT(submission_views.submission_id) AS id_count, submission_data.id, submission_data.thumbnail_source, submission_data.title
      FROM submission_data JOIN submission_views ON submission_views.submission_id = submission_data.id
      WHERE submission_data.type = 'game' AND submission_views.timestamp > DATE_SUB(NOW(), INTERVAL 7 DAY) AND submission_views.timestamp < NOW()
      GROUP BY submission_data.id
      ORDER BY id_count DESC