CODEWITHSUNDEEP
sqlmysql
renevdkooi
renevdkooi

Reputation: 1643

Mysql query deducting 2 counts result less 0

I have a a query which retrieves 2 times a count from 2 tables. Now in the same query it has (countresult1-countresult2) AS restresult

Now restresult is sometimes less than 0 (eq -10) but I want it to return 0 if it's under 0.

Uhm did I explan that right? Minimum value should be 0 not below.

Cheers!!!

Upvotes: 2

Views: 148

Answers (5)

Paul
Paul

Reputation: 207

Until Now I didn't know there was if-else commands in SQL, but I found some.

you will want to use:

WHEN (countresult1-countresult2) < 0 THEN 0 ELSE (countresult1-countresult2)

Here is the source where I found the SQL information: http://www.tizag.com/sqlTutorial/sqlcase.php

Upvotes: 0

Ren&#233; Wolferink
Ren&#233; Wolferink

Reputation: 3548

Take the maximum of 0 and the value you calculated, like this:

SELECT GREATEST(your-restresult-query,0)
FROM ... (etc)

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425573

GREATEST((countresult1-countresult2), 0) AS restresult

Upvotes: 3

ajreal
ajreal

Reputation: 47321

if (countresult1<countresult2, 0, countresult1-countresult2) as restresult

neither countresult1 nor countresult2 will return a negative number, so above should be safe

Upvotes: 1

DRapp
DRapp

Reputation: 48149

without seeing your query, you could have something like...

MAX( if( countresult1-countresult2 < 0, 0, countresult1-countresult2 )) as YourResult

Upvotes: 0

Related Questions