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  gt   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

User · Accepted Answer

Select      Count Distinct user id  As countUsers      Count site id  As countVisits      site id As site  From cp visits  Where ts  gt   DATE SUB NOW    INTERVAL 1 DAY   Group By site id

User · Answer

Overall  SELECT        COUNT DISTINCT  site id   as distinct sites   FROM  cp visits   WHERE ts  gt   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  gt   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

User · Answer

You need to use a group by clause   SELECT  site id  MAX ts  as TIME  count    group by site id

[mysql] MySQL COUNT DISTINCT

Examples related to mysql