SQL issue with NULL values on SUM

Question

I'm currently working on some sql stuff, but running in a bit of an issue.

I've got this method that looks for cash transactions, and takes off the cashback but sometimes there are no cash transactions, so that value turns into NULL and you can't subtract from NULL. I've tried to put an ISNULL around it, but it still turns into null.

Can anyone help me with this?

;WITH tran_payment AS
(
SELECT 1 AS payment_method, NULL AS payment_amount, null as tran_header_cid
UNION ALL
SELECT 998 AS payment_method, 2 AS payment_amount, NULL as tran_header_cid
), 
paytype AS
(
SELECT 1 AS mopid, 2 AS mopshort
),
tran_header AS
(
SELECT 1 AS cid
)
            SELECT p.mopid                     AS mopid,
                   p.mopshort                  AS descript,
                   payment_value AS PaymentValue,  
                   ISNULL(DeclaredValue, 0.00) AS DeclaredValue
            from   paytype p
                   LEFT OUTER JOIN (SELECT CASE 
                       When (tp.payment_method = 1) 
                       THEN
                     (ISNULL(SUM(tp.payment_amount), 0)
                     - (SELECT ISNULL(SUM(ABS(tp.payment_amount)), 0)
                           FROM tran_payment tp
                           INNER JOIN tran_header th on tp.tran_header_cid = th.cid
        WHERE payment_method = 998
        ) )
     ELSE SUM(tp.payment_amount)
     END as payment_value,
     tp.payment_method,
     0   as DeclaredValue
     FROM   tran_header th
     LEFT OUTER JOIN tran_payment tp
     ON tp.tran_header_cid = th.cid
     GROUP  BY payment_method) pmts
     ON p.mopid = pmts.payment_method

Please give your whole query. It just seems to start out of nowhere with paytype p at the moment. — Martin Smith
– Martin Smith, Commented May 12, 2011 at 9:26

Kristof Claes · Accepted Answer · 2011-05-12 09:46:54Z

5

Maybe COALESCE() can help you?

You can try this:

SUM(COALESCE(tp.payment_amount, 0))

or

COALESCE(SUM(tp.payment_amount), 0)

COALESCE(arg1, arg2, ..., argN) returns the first non-null argument from the list.

answered May 12, 2011 at 9:46

Kristof Claes

11k3 gold badges33 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bpgergo · Accepted Answer · 2011-05-12 09:20:45Z

2

try to put ISNULL inside SUM and ABS, i.e. around the actual field, like this

SUM(ISNULL(tp.payment_amount, 0))

SUM(ABS(ISNULL(tp.payment_amount, 0)))

answered May 12, 2011 at 9:20

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

1 Comment

Martin Smith Over a year ago

Shouldn't make any difference NULL values are ignored in calculating the SUM. The only time that SUM(x) will return NULL will be if all the inputs are NULL then just wrapping the aggregate in ISNULL will have the same effect.

Daniel Hilgarth · Accepted Answer · 2011-05-12 09:24:02Z

1

I don't have MS SQL to test here, but would it work to put the ISNULL around the SELECT? Maybe, ISNULL isn't triggered at all, if there are no matching rows...

answered May 12, 2011 at 9:24

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Collectives™ on Stack Overflow

SQL issue with NULL values on SUM

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related