Take the 2-minute tour ×

I have a huge predicament. I need to select records out of a subscription table where status 1 is subscribed and 0 is unsubscribed.

This select must be for the current month BUT i need to select a count of the users that unsubscribed within 24 hrs.

This is how i select the month's statistic

SELECT COUNT(User.id)  AS `count` 
FROM users AS `User` 
LEFT JOIN log_subscriptions AS LogSubscription 
    ON User.id = LogSubscription.user_id 
    ((LogSubscription.status = 1) 
    OR (LogSubscription.status = 0) 
    AND LogSubscription.created BETWEEN '2012-03-01 00:00:01' AND '2012-03-31 23:59:59') 
GROUP BY User.gsm_code, User.site_id, User.keyword_id 
HAVING `count` >= 2

How would i get the people that subscribed and unsubscribed within a 24 hour period by using something similar to this query or am I on the wrong track completely

share|improve this question
Can you show us how your table looks like? –  TJHeuvel Apr 2 '12 at 9:07
I can't i wish i could, its against my companies policies. I can get fired for that. But i am allowed to show queries. –  Ryan Apr 2 '12 at 9:13
@Ryan: Does your log_subscriptions table record the date when the user became unsubscribed? –  Mark Bannister Apr 2 '12 at 9:45
Yes. There is a status column so when they subscribe it places a record in the table with status = 1 and when they unsubscribe it inserts a record as status = 0. So ultimately there are 2 records per user –  Ryan Apr 2 '12 at 10:58

1 Answer 1

You can use DATE_SUB with INTERVAL set to 24 HOUR

    ((LogSubscription.status = 1) 
    OR (LogSubscription.status = 0) 
    AND LogSubscription.created BETWEEN DATE_SUB(NOW(), INTERVAL 24 HOUR) AND NOW()


share|improve this answer
This is helpful BUT it gives me results for people subscribing and unsubscribing within the last 24 hours. I need the count of users for the entire month that unsubscribed within 24 hours. I dont know if that makes any sense –  Ryan Apr 2 '12 at 11:13
It kinda makes sense but I'm commuting now and can only edit my answer later, sorry :( –  SiGanteng Apr 2 '12 at 11:20

Your Answer


