CUENTA DE MySQL DISTINTA

Estoy tratando de recopilar el número de visitas distintas en mi cp ayer, luego contarlas.

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

Por alguna razón, esto está obteniendo múltiples resultados con el mismo id de sitio....¿cómo obtengo y cuento los distintos inicios de sesión de cp site_id?

 123
mysql
Author: Anax, 2011-04-21
Source

3 answers

 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
 257
Author: ypercubeᵀᴹ,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-04-20 23:15:12

En general 

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

O por sitio

  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`

Tener la columna time en el resultado no tiene sentido, ya que está agregando las filas, mostrar un time en particular es irrelevante, a menos que sea el min o max que está buscando.

 18
Author: RichardTheKiwi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-04-20 23:15:33

Necesita usar una cláusula group by.

SELECT  site_id, MAX(ts) as TIME, count(*) group by site_id
 7
Author: Byron Whitlock,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-04-20 23:06:17