Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Converting Null Sum to zero in MS SQL Server 2005 Expand / Collapse
Author
Message
Posted Monday, July 9, 2012 3:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 7:26 AM
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"
Post #1327169
Posted Monday, July 9, 2012 3:10 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
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
Post #1327176
Posted Monday, July 9, 2012 3:12 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020

at the top of your script set concat nulls on then off at bottom :)

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 hardest
Post #1327177
Posted Monday, July 9, 2012 3:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 7:26 AM
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}
Post #1327183
Posted Monday, July 9, 2012 3:30 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
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
Post #1327184
Posted Monday, July 9, 2012 3:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 7:26 AM
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"
Post #1327186
Posted Monday, July 9, 2012 4:28 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 8:18 PM
Points: 17,824, Visits: 15,756
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
Post #1327200
Posted Tuesday, July 10, 2012 3:42 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020
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 :)

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

The first is always the hardest
Post #1327370
Posted Tuesday, July 10, 2012 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 7:26 AM
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?
Post #1327872
Posted Tuesday, July 10, 2012 1:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
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)
Post #1327877
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse