Adding together two column sums in SQL Server2005

Ive come across a problem when trying to add together two column sums. Ive created a view with all the correct data in but when i try to execute a query like:

    Select ID, Sum(开发者_运维百科ColumnA),
    Sum(ColumnB)
    Sum(ColumnA) + Sum(ColumnB) AS ColumnC
    From View1
   Group by ID

The ColumnC figure is only correct when there is data in both columns, if there is only data in ColumnB then it displays that but if there is only data in ColumnA then it doesnt.

Sometime when there isnt any data in ColumnA or B it will be NULL, so maybe this is the problem. Hope there is a way around this.

Cheers

Try using ISNULL to substitute zeros for NULLs:

Select ID, Sum(ColumnA),
Sum(ColumnB)
ISNULL(Sum(ColumnA),0) + ISNULL(Sum(ColumnB),0) AS ColumnC
From View1
Group by ID

Adding something to a null value gives a null result, the null is not converted to zero. You have to do that conversion explicitly:

Select ID, Sum(ColumnA),
Sum(ColumnB)
isnull(Sum(ColumnA), 0) + isnull(Sum(ColumnB), 0) AS ColumnC
From View1
Group by ID

You can use COALESCE to replace NULL inputs to the calculation with zero as below.

COALESCE(Sum(ColumnA),0) + COALESCE(Sum(ColumnB),0) +  AS ColumnC

Or ISNULL as in the other 2 answers. Doesn't matter which if portability is not a concern.