Converting Null Sum to zero in MS SQL Server 2005

  • 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"

  • 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[/url]
    Learn Extended Events

  • 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 step is always the hardest *******

  • 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}

  • 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[/url]
    Learn Extended Events

  • 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"

  • 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[/url]
    Learn Extended Events

  • 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 step is always the hardest *******

  • 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?

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Awesome. Jason had left out one ISNULL and that zero at the end. That did it. Perfect. Thank you Jason and Sean for your help. I appreciate it very much

  • You are welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SGT_squeequal (7/10/2012)


    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 🙂

    I know the solution for this thread went away from it eventually but it is worth mentioninng that it has been the recommendation since SQL 2005 to avoid using CONCAT_NULL_YIELDS_NULL in new development.

    SET CONCAT_NULL_YIELDS_NULL (Transact-SQL) - SQL Server 2005

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply