Group by 2 columns and count
I have the following MySql table - user_actions: id - int user_id - int timestamp - datetime action - int
a data example:
1|5|01.01.2011 21:00:00|1
2|5|01.01.2011 21:00:00|3
3|6|01.01.2011 21:00:00|5
3|6|01.02.2011 21:00:00|5
I want to count the number of users who made a开发者_StackOverflown action in each day (no matter how many actions), in the above example the output should be:
01.01.2011|2
01.02.2011|1
- meaning 2 users made actions in 01.01.2011 and one user in 01.02.2011
any thoughts?
SELECT count(DISTINCT user_id)
, DATE_FORMAT(timestamp, '%Y-%m-%d')
from table
group by DATE_FORMAT(timestamp, '%Y-%m-%d')
精彩评论