Adding with NULL values

8 Jul 2005 5:59 AM

ninel

I have 3 columns that need to be summed up, but any one of these can contain a NULL value. Col1 Col2 Col3 Col4 A 2.67 4.7 2.8 B 6.2 NULL 2.6 C 12.4 NULL NULL How can I sum them up to look like this? A = 10.17 B = 8.8 C = 12.4 Thanks, Ninel

8 Jul 2005 6:44 AM

Uri Dimant

Ninel

CREATE TABLE #Test
(
col1 CHAR(1),
col2 DECIMAL(5,2),
col3 DECIMAL(5,2),
col4 DECIMAL(5,2)
)
INSERT INTO #Test VALUES ('A',2.67,4.7, 2.8)
INSERT INTO #Test VALUES ('A',6.2,NULL, 2.6)
INSERT INTO #Test VALUES ('A',12.4,NULL, NULL)

SELECT COALESCE(col2,0)+COALESCE(col3,0)+COALESCE(col4,0)
FROM #Test

Show quote

"ninel" <ngorbu***@onetouchdirect-dot-com.no-spam.invalid> wrote in message
news:_cWdnVFmD8TRi1PfRVn_vg@giganews.com...
> I have 3 columns that need to be summed up, but any one of these can
> contain a NULL value.
>
> Col1 Col2   Col3    Col4
> A     2.67    4.7     2.8
> B     6.2     NULL    2.6
> C     12.4    NULL    NULL
>
> How can I sum them up to look like this?
>
> A = 10.17
> B = 8.8
> C = 12.4
>
> Thanks,
> Ninel
>