help on calculation including nulls

Hi everybody

I have this sql which calculates the total

MY TABLE IS:

A B C

100 5 3
100 0 5
20 0 0

sql is

A - B + C as total

the result is it gives me only the total of the first row since it has values on the on the B AND C

the result is

A B C total

100 5 3 98
100 0 5
20 0 0

with no result for the last two rows.. I want to give a condition that if the value is zero or null go to the next...

thanks for any helpRead up on the COALESCE function in the (online) help files.

COALESCE(Field, ValueIfNull)|||Thanks solved my problem and thanks for reminding me on the way of saying thanks in advance..|||you can also use isnull-

select a, b, c, (isnull(A,0) - isnull(B, 0) + isnull(C,0)) as total|||coalesce is generally preferred as it's ANSI. also it can take multiple args.|||Yes thats right, COALESCE have several advantages over ISNULL

http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html
http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx (http://http://odetocode.com/Blogs/scott/archive/2004/11/30/679.aspx)

readability is the only advantage of using ISNULL.|||readability is the only advantage of using ISNULL.

readability? what's more readable:

select ISNULL(a, ISNULL(b, ISNULL(c,d)))

select COALESCE(a,b,c,d)

obviously I prefer coalesce. :)|||IsNull is more readable imo.
Before I learnt of coalesce I had no idea what it meant.|||IsNull is more readable imo.only for two values, and even then it's six of one, six of the other

ISNULL(foo,0)
COALESCE(foo,0)

now try it with multiple expressions, and see if ISNULL is really more readable|||Readability -

Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output.

this is what databases.aspfaq.com says.

I just wanted to tell alexyeth about the alternative available.
I alway prefer COALESCE.|||IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.

IMO - the fact you don't know what coalesce means is an advantage. Come across it and you have to look it up. Come across ISNULL as a vb programmer and you might think you know what it does.

BOOOOOOOOO to T-SQL ISNULL!|||IMO ISNULL is a stupid name for a function that does not tell you whether or not the input is null. The fact that there is another function of the same name in another MS language that does do this compounds confusion.

I'm pretty sure sql server inherited this function from sybase. doesn't change the fact that ISNULL is a stupid name for the function though. :)|||Goddam Bill lover ;)|||eh, I have a soft spot for sql server, that's all. ;)