adding two columns?

[tipifire]

Does any body know how to get a sum total for two columns? Even if its has null?

Don't assume to know it all. For all answers to your solutions, you should always add the unknown constant. This is the real perfection we strive for.

 

[nigelrivett]

sum(coalesce(col1,0)) + sum(coalesce(col2,0))

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tipifire]

Thank you i knew it had something to do with coalesce but didn't know how. thank you.

i assume i can do this

select sum(coalesce(col1,0)) + sum(colesce(col2,0)), *
from tbltest

Don't assume to know it all. For all answers to your solutions, you should always add the unknown constant. This is the real perfection we strive for.

 

[nigelrivett]

no, you would be trting to sum all columns (1 record) and display all rows individually.
If you want to disply the sum with each row

select (select sum(coalesce(col1,0)) + sum(colesce(col2,0)) from tbltest), *
from tbltest

you can also

select sum(coalesce(col1,0)) + sum(colesce(col2,0)), col1, col2
from tbltest
group by col1, col2

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Lekar]

Hi:

I think you can't place the * character at the end of the columns, because it will include each column of the table, and the aggregate funcions won't work with individual columns unless you code each column in a Group By Clause.

 

[tipifire]

I just put * there for example only I'm really not using the * but thanks for the warning

Don't assume to know it all. For all answers to your solutions, you should always add the unknown constant. This is the real perfection we strive for.

 

[Lekar]

Aahh, with the exception of Count(), aggregate functions ignore null values. So, you even can write:
[tt]
Select Sum(Col_1) + Sum(Cod_2)
From TableTest
[/tt]

;-)

 

[nigelrivett]

>> Select Sum(Col_1) + Sum(Cod_2)

You can do that but may get a warning depending on the ansi warnings setting. Better to cater for the null values.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tipifire]

amazing enough it worked really good. I did not try the "Select Sum(Col_1) + Sum(Cod_2)" but will save it for a rainy day.

Don't assume to know it all. For all answers to your solutions, you should always add the unknown constant. This is the real perfection we strive for.