[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1131
  • Last Modified:

MSSQL Sum w/NULL Column Values

HELLO!

I have a table called "POSTING" with the following values:

EVENT_NO                 AMT         DROP        LOAN
-----------------------------------------------------------------
E-1000117      0.00      NULL      NULL
E-1000117      668.49      -1500.00      NULL
E-1000117      393.80      NULL      NULL
E-1000117      1309.00      NULL      NULL
E-1000117      0.00      NULL      NULL
E-1000117      0.00      NULL      NULL
E-1000117      0.00      NULL      NULL

What I need to achieve...is something like the following:

Select Sum(AMT - DROP + LOAN) as NET_DAILY_TOTAL

However, of course, since there are many 'NULL' values in the DROP and LOAN columns, this will yield a total of 'NULL'...which is erroneous and nonsense.  Please let me know the correct Syntax to handle the 'NULL' values in the example above.  Thank You!...Mark
0
datatechcorp
Asked:
datatechcorp
  • 2
2 Solutions
 
Simone BCommented:
Try this, it should replace your NULLs with 0's then add them.

SELECT SUM(AMT - ISNULL([DROP],0) + ISNULL(LOAN,0)) AS NET_DAILY_TOTAL
0
 
BeartlaoiCommented:
Select Sum(Coalesce(AMT,0)) - Sum(Coalesce(DROP,0)) + Sum(Coalesce(LOAN,0)) as NET_DAILY_TOTAL
0
 
Simone BCommented:
ISNULL and COALESCE will give the same result. But because DROP is also a keyword, you should use [DROP] to avoid syntax errors.
0
 
datatechcorpAuthor Commented:
Thank you both *SO* much for responding!  I truly appreciate it...this worked great!

Thanks!...Mark
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now