CODEWITHSUNDEEP
mysql
Mike
Mike

Reputation: 2225

MySQL COUNT DISTINCT

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?

Upvotes: 156

Views: 339947

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

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.

Upvotes: 26

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115640

 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

Upvotes: 315

Byron Whitlock
Byron Whitlock

Reputation: 53921

You need to use a group by clause.

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

Upvotes: 7

Related Questions