CODEWITHSUNDEEP
mysql
dogmatic69
dogmatic69

Reputation: 7575

Count posts by date for all users

I have users and posts table and would like to get a count of posts by a user for various date spans.

users: id, username
1, bob
2, sam

posts: id, user_id, created (datetime)
1, 1, 2012-12-01 00:00:00
2, 1, 2012-12-30 00:01:00
3, 1, 2012-12-30 01:00:00
4, 2, 2012-12-12 00:00:00
5, 2, 2012-12-30 01:00:00

I want to count the posts for the last hour, day and month. Can this be done in one query?

Say the date the query is run is 2012-12-30 01:30:00 I would want something like the following:

User.id | hour | day | month
1       | 1    | 2   | 3
2       | 1    | 1   | 2

I have tried to something like the following but the numbers are wrong. 

SELECT 
    User.id, 
    COUNT(Hour.id) as hour, 
    COUNT(Day.id) as day, 
    COUNT(Month.id) as month
FROM user as User
LEFT JOIN posts as Hour ON 
    (posts.user_id = user.id AND posts.created >= '2012-12-30 00:30:00')
LEFT JOIN posts as Day ON 
    (posts.user_id = user.id AND posts.created >= '2012-12-29 01:30:00')
LEFT JOIN posts as Month ON 
    (posts.user_id = user.id AND posts.created >= '2012-11-30 01:30:00')

I end up with strange numbers like

User.id | hour | day | month
1       | 12   | 12  | 12
2       | -    | 2   | 2

Upvotes: 1

Views: 116

Answers (1)

fthiella
fthiella

Reputation: 49049

You could use this:

select
  username,
  user_id,
  sum(created>=date_sub('2012-12-30 01:30:00', INTERVAL 1 HOUR)) hour,
  sum(created>=date_sub('2012-12-30 01:30:00', INTERVAL 1 DAY)) day,
  count(*) month
from users inner join posts on users.id=posts.user_id
where created>=date_sub('2012-12-30 01:30:00', INTERVAL 1 MONTH)
group by user_id

(you can then substitute the specific datetime '2012-12-30 01:30:00' with now())

Upvotes: 3

Related Questions