MySQL COUNT DISTINCT

Question

I'm trying to collect the number of distinct visits in my cp yesterday, then count them.

SELECT
    DISTINCT `user_id` as user,
    `site_id` as site,
    `ts` as time
FROM
    `cp_visits`
WHERE
    ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)

For some reason this is pulling multiple results with the same site id....how do i only pull and count the distinct site_id cp logins?

ypercubeᵀᴹ · Accepted Answer · 2011-04-20 23:15:12Z

315

 Select
     Count(Distinct user_id) As countUsers
   , Count(site_id) As countVisits
   , site_id As site
 From cp_visits
 Where ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)
 Group By site_id

answered Apr 20, 2011 at 23:15

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Over a year ago

I love SQL because you get questions like "How do I count distinct user IDs" and the answer is just "Count(Distinct user_id)"

Alexey Sh. Over a year ago

for mysql it could return an error because user_id isn't in group by clause

ypercubeᵀᴹ Over a year ago

@AlexeySh. , no it will not give any error.

RichardTheKiwi · Accepted Answer · 2011-04-20 23:15:33Z

26

Overall

SELECT
       COUNT(DISTINCT `site_id`) as distinct_sites
  FROM `cp_visits`
 WHERE ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)

Or per site

  SELECT
         `site_id` as site,
         COUNT(DISTINCT `user_id`) as distinct_users_per_site
    FROM `cp_visits`
   WHERE ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY `site_id`

Having the time column in the result doesn't make sense - since you are aggregating the rows, showing one particular time is irrelevant, unless it is the min or max you are after.

answered Apr 20, 2011 at 23:15

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Comments

Byron Whitlock · Accepted Answer · 2011-04-20 23:06:17Z

7

You need to use a group by clause.

SELECT  site_id, MAX(ts) as TIME, count(*) group by site_id

answered Apr 20, 2011 at 23:06

Byron Whitlock

54.2k29 gold badges128 silver badges170 bronze badges

Collectives™ on Stack Overflow

MySQL COUNT DISTINCT

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related