Total Rows with Null Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Total Rows with Null Value

Author

Topic

bbowser
Starting Member

43 Posts

Posted - 2009-01-05 : 14:56:23

Can someone please tell me how to total a row with a Null value in one column. I need to total the rows even if they have a Null value. Here is what I've tried and the result.

Select ScreenMat, ScreenLab, DoorMat, DoorLab, ScreenMat + ScreenLab + DoorMat + DoorLab as Total
From Transactiontbl

Transactiontbl

ScreenMat ScreenLab DoorMat DoorLab Total
$20.00 $40.00 $20.00 $40.00 $120.00
$30.00 Null $30.00 $40.00 Null
$20.00 $40.00 $20.00 Null Null

Thanks in advance guys & gals

-B

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-05 : 15:12:40

quote:
Originally posted by bbowser

Can someone please tell me how to total a row with a Null value in one column. I need to total the rows even if they have a Null value. Here is what I've tried and the result.

Select ScreenMat, ScreenLab, DoorMat, DoorLab, ScreenMat + ScreenLab + DoorMat + DoorLab as Total
From Transactiontbl

Transactiontbl

ScreenMat ScreenLab DoorMat DoorLab Total
$20.00 $40.00 $20.00 $40.00 $120.00
$30.00 Null $30.00 $40.00 Null
$20.00 $40.00 $20.00 Null Null

Thanks in advance guys & gals

-B

I would use the ISNULL function to replace null values with 0. This way you will get a total for each row. Aggregate functions like SUM will ignore columns with null btw..

select SUM(isnull(ScreenMat,0)+ isnull(ScreenLab,0)+ isnull(DoorMat,0)+ isnull(DoorLab,0)) AS Total
from YourTable

perhaps there is a more elegant solution?

r&r

bbowser
Starting Member

43 Posts

Posted - 2009-01-05 : 15:29:41

Thanks revdnrdy but I need to total the rows and not the columns. As in the first row I receive the total of $120.00 which is what I want, however if a row has a null value in it then it returns a Null value for the row total as seen in row 2 and row 3 results. Any other ideas?

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-05 : 15:36:27

Using my example..

simply remove the aggregate function SUM.

change from SUM(isnull(blah,0)+isnull(blah2,0))
to isnull(blah,0)+isnull(blah2,0)

and it will calculate each row individually as you want.

tested and verified on sql 2005..

r&r

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-05 : 15:39:25

[code]Select
ScreenMat,
ScreenLab,
DoorMat,
DoorLab,
isnull(ScreenMat,0) + isnull(ScreenLab,0) + isnull(DoorMat,0) + isnull(DoorLab,0) as Total
From Transactiontbl[/code]

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-05 : 15:40:17

Oh - sorry!
Toooooooooo late