Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting Null Sum to zero in MS SQL Server 2005


Converting Null Sum to zero in MS SQL Server 2005

Author
Message
SeanRichardson09
SeanRichardson09
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
I used "Blahs" just for this example: One of these queries will sometimes add up to be zero (No payments were made that day in that market to add up) which makes it NULL so even though the second query will have a valid SUM of payments made that day the whole query will add up to NULL just because the first query added up to zero. Is there any way to make the first query to say 0 instead of null so it'll properly add the totals? 1st query adds up to zero and second query adds up to 100. I want it to say 0 + 100 = 100. Right now its saying: Null + 100 = NULL. Thanks a lot.

SELECT
(Select SUM (Amount) From blah1.dbo.Paymenthistory
where Billdesc like '%Blah Blah%'
and Receivedate >= '6/30/2012 00:00:00'
and Receivedate <= '6/30/2012 23:59:59')

+

(Select SUM (Amount) From blah2.dbo.Paymenthistory
where Billdesc like '%Blah Blah%'
and Receivedate >= '6/30/2012 00:00:00'
and Receivedate <= '6/30/2012 23:59:59')

AS "Blah + Blah"
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21099 Visits: 18259

SELECT
(Select SUM (ISNULL(Amount,0)) From blah1.dbo.Paymenthistory
where Billdesc like '%Blah Blah%'
and Receivedate >= '6/30/2012 00:00:00'
and Receivedate < '6/30/2012 23:59:59')

+

(Select SUM ((ISNULL(Amount,0)) From blah2.dbo.Paymenthistory
where Billdesc like '%Blah Blah%'
and Receivedate >= '6/30/2012 00:00:00'
and Receivedate < '6/30/2012 23:59:59')

AS "Blah + Blah"





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SGT_squeequal
SGT_squeequal
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 1047
at the top of your script set concat nulls on then off at bottom Smile

SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

*************************************************************

The first is always the hardestw00t
SeanRichardson09
SeanRichardson09
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
So like this? Sorry I have not used SET Command before. I'm pretty new to SQL. This didnt work in this format, but I think it is wrong the way I set it up. It says:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'ON'.
Msg 156, Level 15, State 1, Line 65
Incorrect syntax near the keyword 'OFF'.



SET CONCAT_NULL_YIELDS_NULL {ON}

SELECT

(Select SUM (Amount) From Blah1.dbo.Paymenthistory)

+

(Select SUM (Amount) From Blah2.dbo.Paymenthistory)


As "Blah + Blah"

SET CONCAT_NULL_YIELDS_NULL {OFF}
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21099 Visits: 18259
just use the script example I provided.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SeanRichardson09
SeanRichardson09
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
I tried and I really thought that was going to work but it didn't. Is this set up right? It still says my total = NULL. I'll give you my full queries to see if anything else if affecting that, but it shouldnt.

SELECT

(-- Florida CC Payments --)
Select SUM (ISNULL(Amount, 0)) as "Total Commodity Paid for Florida"
From Contracts.dbo.Paymenthistory
INNER JOIN Contracts.DBO.Serviceaddr
ON Contracts.dbo.Paymenthistory.ServiceAddrID = Contracts.dbo.Serviceaddr.ServiceaddrKey
where Contracts.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and Contracts.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and Contracts.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and Contracts.dbo.Paymenthistory.amount !< '0'
and Contracts.dbo.Paymenthistory.BillDesc not like '%Reapplied%')

+

(-- Georgia CC Payments --)
Select SUM (ISNULL(Amount,0)) as "Total Commodity Paid for Georgia"
From Phase1.dbo.Paymenthistory
INNER JOIN Phase1.DBO.Serviceaddr
ON Phase1.dbo.Paymenthistory.ServiceAddrID = Phase1.dbo.Serviceaddr.ServiceaddrKey
where Phase1.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and Phase1.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and Phase1.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and Phase1.dbo.Paymenthistory.amount !< '0'
and Phase1.dbo.Paymenthistory.BillDesc not like '%Reapplied%')

+

(-- NorthEast CC Payments --)
Select SUM (ISNULL(Amount,0)) as "Total Commodity Paid for NorthEast"
From NY.dbo.Paymenthistory
INNER JOIN NY.DBO.Serviceaddr
ON NY.dbo.Paymenthistory.ServiceAddrID = NY.dbo.Serviceaddr.ServiceaddrKey
where NY.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and NY.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and NY.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and NY.dbo.Paymenthistory.amount !< '0'
and NY.dbo.Paymenthistory.BillDesc not like '%Reapplied%'
and state != 'TX')

+

(-- Georgia Deposit Payments --)
Select SUM (ISNULL(Amount,0)) AS "Total Deposit Payments for Georgia"
From Phase1.dbo.AccountDepositActivity
JOIN Phase1.DBO.Serviceaddr
ON Phase1.dbo.AccountDepositActivity.ServiceAddrID = Phase1.dbo.Serviceaddr.ServiceaddrKey
where DepositType = '2'
And Phase1.dbo.AccountDepositActivity.PostDate >= '6/30/2012 00:00:00'
And Phase1.dbo.AccountDepositActivity.PostDate <= '6/30/2012 23:59:59'
And Phase1.dbo.AccountDepositActivity.UI = 'Adept - CS:creditcard')


As "Total Money From CC Payments For the Day"
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21099 Visits: 18259
One of those subselects returns no data so it results in null. You can get around that with another isnull or a query rewrite. If you provided sample data and table structures, somebody could help rewrite that query.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SGT_squeequal
SGT_squeequal
SSC Veteran
SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)SSC Veteran (276 reputation)

Group: General Forum Members
Points: 276 Visits: 1047
looks like i stand corrected and you cant use SET CONCAT_NULL_YIELDS_NULL { ON | OFF } for calculations of int field so use isnull function Smile

*************************************************************

The first is always the hardestw00t
SeanRichardson09
SeanRichardson09
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
Ok so in this case, there were zero payments made for the first query. The query does not find any results, so it says the result is NULL.


First Query


Select SUM(ISNULL(Amount, 0)) From Contracts.dbo.Paymenthistory
where Contracts.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and Contracts.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and Contracts.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and Contracts.dbo.Paymenthistory.amount !< '0'
and Contracts.dbo.Paymenthistory.BillDesc not like '%Reapplied%'


Results: NULL
(No payments made on june 30th 2012: No Results. If it were Select * From... then it would have no results)


The second query:

Select SUM(ISNULL(Amount, 0)) From Phase1.dbo.Paymenthistory
where Phase1.dbo.Paymenthistory.Billdesc like '%Credit Card%'
and Phase1.dbo.Paymenthistory.Receivedate >= '6/30/2012 00:00:00'
and Phase1.dbo.Paymenthistory.Receivedate <= '6/30/2012 23:59:59'
and Phase1.dbo.Paymenthistory.amount !< '0'
and Phase1.dbo.Paymenthistory.BillDesc not like '%Reapplied%'

Results: 7500.74

And if you change this query to say Select * From.... then the results for the Amount column are below which adds up to 7500.74. Thats 152 different payments adding up to 7500.74

45.97
88.21
41.19
57.61
58.30
50.05
28.70
35.72
32.52
31.54
52.35
67.37
36.28
35.95
28.83
42.19
60.46
79.97
35.86
62.95
40.96
60.27
32.72
109.21
122.90
44.77
39.30
45.00
43.35
47.66
31.19
28.87
79.84
136.96
4.95
50.52
9.34
125.70
70.57
32.53
81.47
97.31
35.26
68.49
88.68
6.43
26.04
39.84
30.56
105.19
76.59
8.05
41.88
34.39
67.27
36.13
31.43
22.97
42.32
52.44
32.78
29.45
34.92
33.55
2.45
32.23
0.01
55.67
38.12
37.62
12.63
44.10
52.50
38.58
93.11
57.52
67.87
43.49
40.68
38.81
60.63
47.78
100.00
79.74
36.63
41.71
79.95
54.86
40.91
95.19
28.67
60.87
47.56
5.17
79.95
43.38
29.97
33.21
35.45
51.85
47.80
35.41
35.84
35.21
50.43
31.85
51.14
28.69
35.48
50.66
41.01
49.98
41.04
27.34
38.89
17.66
41.65
50.33
45.80
30.38
34.18
38.44
38.55
62.79
16.37
36.93
37.88
47.74
53.44
59.95
30.25
39.07
40.13
46.57
37.07
114.36
86.83
47.37
29.38
109.21
65.95
73.06
41.87
42.95
117.36
28.67
41.33
100.00
97.36
43.37
30.81
63.97

Now If I were to add both queries together, it would say that NULL + 7500.74 = NULL which is my problem. I want it to know that there were zero payments made in the first query which in turn means the the amount is zero not NULL. I want it to say 0 + 7500.74 = 7500.74. IF THIS HELPS I'm not sure if I need to somehow convert the amount column to something else. The direct Object Explorer says that the amount column is this: Amount (decimal(18,2), null). There will be no amounts that = 0 in the actual data. I just want the sum of zero payments to = 0. Is this possible?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
As Jason already said you need to wrap your sum with an ISNULL.


Select ISNULL(SUM(ISNULL(Amount, 0)), 0)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search