CODEWITHSUNDEEP
mysqlcountdatediff
SRL
SRL

Reputation: 77

Mysql Count datediff total rows if field value more than 1 and not less than zero

the purpose of creating a post is, I can not find an example of a case in accordance with the case that I'm experiencing right now, I want to take the amount from the due date for more than 1 instead of minus or equal to zero

I'm sorry if it had already existed posting the same, because it does not find an example of an appropriate case. I just need an example in accordance with the example of my case

my db

id          userid          duedate                status
1           lexter          2016-01-27          not yet paid
2           jemmy           2016-02-01          not yet paid
3           zaya            2016-03-02          already paid
4           randy           2016-08-09          not yet paid
5           max             2016-03-08          not yet paid
6           neo             2016-03-14          already paid
7           nemo            2016-01-09          not yet paid

this mysql query

SELECT duedate,
     current_date() as datenow,
     count(datediff(current_date(), duedate) as late_payment) as late_payment
FROM
    mydb
WHERE
    status='not yet paid'

my query only count all of record although he is less than zero or greater than zero, and i want only the record greater than zero and not yet paid will be count.

Upvotes: 0

Views: 1248

Answers (1)

Shadow
Shadow

Reputation: 34285

This is called conditional counting and you can do it via sum() or count(). You need to place the case statement or if() within the group by functions. I'll show the example for the count():

SELECT duedate,
     current_date() as datenow,
     count(case when datediff(current_date(), duedate) >0 then 1 else null end) as late_payment_count
FROM
    mydb
WHERE
    status='not yet paid'
group by duedate, current_date()

You can simplify the condition, because datediff(current_date(), duedate) >0is actually the same as current_date() > duedate.

If your are interested in the number of late payments only, then you can further simplify your query as 

SELECT duedate,
     current_date() as datenow,
     count(*) as late_payment_count
FROM
    mydb
WHERE
    status='not yet paid' and current_date() > duedate
group by duedate, current_date()

Upvotes: 2

Related Questions