CODEWITHSUNDEEP
mysqlsqlgroup-bysum
Martin Fric
Martin Fric

Reputation: 730

Count unique days grouped by user

I have the following SQL table 

|user|log_date            |
| 2  |2016-06-23 10:55:52 |
| 2  |2016-06-23 10:55:54 |
| 2  |2016-06-24 10:53:54 |
| 2  |2016-06-24 10:54:54 |

and so on with many other users and log_dates. What I want is to check for whole month :

where left(log_date,7)="2016-06"

But I want to count one day only once per day. So the result for my table in this example should be :

|user|count of unique days|
| 2  |         2          |

and I want it to be grouped by users. So for every user in table I want to count unique days.

Can anybody give me a hint?

Upvotes: 1

Views: 109

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Writing the condition this way allows the use of an index...

SELECT t.user
     , COUNT(DISTINCT DATE(t.log_date)) unique_days
  FROM my_table t
 WHERE t.log_date BETWEEN '2016-06-01 00:00:00' AND '2016-06-31 23:59:59' -- or t.log_date >= '2016-06-01 00:00:00' AND t.log_date < '2016-07-01 00:00:00' 
 GROUP 
    BY t.user;

(Not sure why Sagi deleted their answer after correcting it)

Upvotes: 1

Blank
Blank

Reputation: 12378

Try this:

select
    user,
    count(distinct day(log_date)) as `count of unique days`
from yourtable
where left(log_date, 7) = '2016-06'
group by user

SQLFiddle Demo

Upvotes: 0

Related Questions