Menu
To count distinct values, you can use distinct in aggregate function count(). The result i3 tells that we have 3 distinct values in the table.
Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows.
The COUNT DISTINCT function returns the number of unique values in the column or expression, as the following example shows. SELECT COUNT (DISTINCT item_num) FROM items; If the COUNT DISTINCT function encounters NULL values, it ignores them unless every value in the specified column is NULL.
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
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.
You need to use a group by clause.
SELECT site_id, MAX(ts) as TIME, count(*) group by site_id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With