CODEWITHSUNDEEP
mysqldatecountsumrow
Wilf
Wilf

Reputation: 2315

MySQL : Count row where sum of left day is less than 30

I want to count the users in the table who's subscriptions are going to expire within a month (30 days). Here is my code:

user_db

id         name        exp_date
1          John        2013-03-01
2          Alice       2013-02-25
3          Ken         2013-01-10
4          Elise       2013-04-11
5          Bruce       2013-03-14

According to the DB above. There should be 3 persons whom their subscription is about to be expired - John, Alice and Bruce. I don't want Ken to be counted because he doesn't want to subscribe for more.

Here's my MySQL code:

SELECT count(id) AS exp_pax, 
       datediff(exp_date,now()) AS day_left 
FROM labour_db 
WHERE day_left<=30

Well, the code does selects only a row in which the sum of day less than 30 but it doesn't count. So please you guy suggest me.

Regards,

Upvotes: 0

Views: 1257

Answers (3)

D Mac
D Mac

Reputation: 3809

If you want to count all records where (1) the expiration date is within 30 days of now and (2) the expiration date is not before now, then use

SELECT count(*) AS exp_pax 
FROM   user_db 
WHERE exp_date<=timestampadd(day, 30, now())
       AND exp_date >= now();

Upvotes: 1

John Woo
John Woo

Reputation: 263813

If that's the case then you need to add condition wherein it checks if the exp_date is less than today.

SELECT  COUNT(*) totalCount
FROM    user_db 
where   exp_date <= timestampadd(day, 30, now()) AND
        exp_date > NOW()

Upvotes: 1

Joe Mastey
Joe Mastey

Reputation: 27119

Remove the group by id to get your count.

Count will roll up rows, as you expect, but when combined with a group by clause, will count each "group". You could use this usefully, for instance, to group by expiration date.

Upvotes: 0

Related Questions