CODEWITHSUNDEEP
mysqldistinct
qqmydarling
qqmydarling

Reputation: 177

MySQL DISTINCT doesn't work with GROUP BY timestamp

Im trying to get data for google chart. I want to get a sum unique values for every hour of the each day. But DISTINCT doesn't work with GROUP BY.

SELECT DATE_FORMAT(created_at, "%Y-%m-%d %H:00:00") as date,
COUNT(DISTINCT visitor_id) as visitors FROM `visitors`  
WHERE web='example.com' AND created_at BETWEEN '2017-11-02 00:00:00' AND '2017-11-02 23:59:59'

The result without GROUP BY will be.

date                   visitors
---------------------|----------|
2017-11-02 14:00:00  |   2168   |

The result with GROUP BY date will be 

date                   visitors
---------------------|----------|
2017-11-02 00:00:00  |    19    | 
2017-11-02 01:00:00  |    16    |
2017-11-02 02:00:00  |    18    |
...                       ...
total will be 2749

I want to reach the second result but with right sum of unique values

And the second question is how can I be sure that after few days my unique value won't be assigned to the other date?

Upvotes: 0

Views: 339

Answers (1)

Zamfi
Zamfi

Reputation: 331

You should be able to count the right sum with this:

SELECT DATE_FORMAT(created_at, "%Y-%m-%d %H:00:00") as date,
COUNT(visitor_id) as visitors

FROM (
  SELECT MIN(created_at) AS created_at, visitor_id
  FROM `visitors`

  WHERE web='example.com'
  AND created_at BETWEEN '2017-11-02 00:00:00' AND '2017-11-02 23:59:59'

  GROUP BY visitor_id
) all_visits

GROUP BY DATE_FORMAT(created_at, "%Y-%m-%d %H:00:00")

Upvotes: 2

Related Questions