开发者

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')
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜