CODEWITHSUNDEEP
mysql
user3692459
user3692459

Reputation: 162

Count Number of rows with negative integers in mysql

i have a timestamp column in my DB and i want to sum the number of rows that are passed due, let me show you the query for that!

SELECT TIMESTAMPDIFF(MINUTE, `Opened`, NOW()) as PastDue FROM `Task`

not this query returns the perfect result and return all rows where past due is negative and others are positive

now i want to count the number of negative rows so i did

SELECT COUNT(TIMESTAMPDIFF(MINUTE, `Opened`, NOW())) < 0 as PastDue FROM `Task`

it currently returns 0 now there are two rows with negative result so it should return two but it always returns 0 , can anyone point out what i am missing?

Upvotes: 1

Views: 146

Answers (1)

Nick
Nick

Reputation: 147206

COUNT(TIMESTAMPDIFF(MINUTE, `Opened`, NOW())) < 0

is a boolean expression (checking whether the COUNT is less than 0), which will always return 0 (false) since a COUNT must be at least 0. What you can do instead is SUM the results of the test (since MySQL treats booleans as 0 or 1 in a numeric context):

SELECT SUM(TIMESTAMPDIFF(MINUTE, `Opened`, NOW()) < 0) as PastDue FROM `Task`

Note that you can simplify this query to just compare Opened with NOW(), either in the SUM:

SELECT SUM(`Opened` > NOW()) AS PastDue
FROM `Task`

or using COUNT(*) with a WHERE clause:

SELECT COUNT(*) AS PastDue
FROM `Task`
WHERE `Opened` > NOW()

Note that based on the wording of your question, you probably want to change the condition from

`Opened` > NOW()

to

`Opened` < NOW()

Upvotes: 2

Related Questions