CODEWITHSUNDEEP
mysqlsqldistincthour
Eric
Eric

Reputation: 1

SQL - Unique Values By Hour

I have a table VISITS where I want to look at all DEVICES and get a unique count of USERS by HOUR each DAY ([parsed from TIME 2014-05-01 07:01:04). After an hour, the USER could be part of another HOUR of that day.

So, the output should show:

DAY     HOUR   DEVICE   USERS
3/1/15   0       Dev1     240
3/1/15   0       Dev2     123
...
3/1/15   23      Dev1      43
3/2/15  ....

Upvotes: 0

Views: 2539

Answers (2)

vol7ron
vol7ron

Reputation: 42139

Without testing this (not sure if MySQL still requires backticks):

SELECT    Date(`time`)         as `day`, 
          Hour(`time`)         as `hour`, 
          device,
          count(DISTINCT user) as `users`
FROM      visits 
GROUP BY  Date(`time`), 
          Hour(`time`), 
          device;

Upvotes: 2

Stephan
Stephan

Reputation: 6018

SELECT day,hour,devices,COUNT(DISTINCT Users)
FROM Visits
GROUP BY day,hour,devices
ORDER BY day,hour,devices

Upvotes: 0

Related Questions