Arithmetic in SQL query when one value might be NULL?

bunner_bob

I have a query something like this:

"SELECT SUM((Price - Adjustment) * Quantity) AS SumPrice WHERE ..." (etc)

The Adjustment field is default NULL, so sometimes (most of the time actually) this value is a NULL, which seems to screw up the results.

Is there some SQL-esque way to say "Price - Adjustment IF Adjustment is not null"?

Or do I "just" need to replace all my nulls with zeroes? Sure hope not 🙂

NogDog

You could use an IF:

$sql = "SELECT SUM((Price - IF(Adjustment IS NULL, 0, Adjustment)) * Quantity) AS . . .";

xblue

Or you could use IFNULL(Adjustment, 0) or - which I would prefer in many cases, but there may be situations where 0 is a legal value that has a different meaning than NULL - consider to default to 0 instead.

bunner_bob

xblue wrote:
Or you could use IFNULL(Adjustment, 0) or - which I would prefer in many cases, but there may be situations where 0 is a legal value that has a different meaning than NULL - consider to default to 0 instead.

Ahh - would 'twer I had thought of that when I first built the app, not now when there are a few hundred live orders in it 🙂 Good advice for next time.

Thanks for the advice. NogDog, I think yours will work great.

Of course I also just discovered that the adjustment has already been calculated into the price in the instance I'm working on, so I don't actually need it. Argh. Only realized it after implementing...

bradgrafelman

bunner bob wrote:
Ahh - would 'twer I had thought of that when I first built the app, not now when there are a few hundred live orders in it Good advice for next time.

How is it too late? Just change your default to '0', and then do something like this:

UPDATE `myTable` SET `Adjustment` = 0 WHERE `Adjustment` IS NULL;

Roger_Ramjet

Have a look at this thread for your answer.

Are you guys doing the same course or something? Strange to see the same rare question asked twice like that.