CODEWITHSUNDEEP
sqlsql-server-2005
MyHeadHurts
MyHeadHurts

Reputation: 1562

sql query subtraction

I have a query where it takes my total sales and subtracts by my canceled sales, however if there are no canceled sales the value is null. then when the

so basically if my canceledsales where null it returns a null value for the subtraction column instead of the totalsales

totalsales-canceledsales(null) = null
1000-null=null

i want it to be like this

1000-null=1000

Upvotes: 1

Views: 1136

Answers (2)

Justin Niessner
Justin Niessner

Reputation: 245399

The proper ANSI SQL way would be (taking into account totalsales and canceledsales could both be null):

coalesce(totalsales, 0) - coalesce(canceledsales, 0)

You may also see the use of ISNULL on SQL Server:

isnull(totalsales, 0) - isnull(canceledsales, 0)

Upvotes: 6

Marcus Adams
Marcus Adams

Reputation: 53830

The issue is that any function against NULL is NULL. In this case, NULL means unknown.

If you subtract a number from an unknown number, the result is unknown.

If you subtract an unknown number from a known number, the result is unknown.

Both numbers must be known in order to return a known answer. In this case if one of the operands is NULL, NULL is the correct answer.

However, if you'd rather see 0 than NULL, then use ISNULL():

totalsales - isnull(canceledsales, 0)

The book SQL AntiPatterns has a whole chapter better explaining NULL.

Upvotes: 0

Related Questions