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?
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
Read more... Read less...
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`
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
max you are after.
You need to use a group by clause.
SELECT site_id, MAX(ts) as TIME, count(*) group by site_id