Geek asks and answers

SQL error with NULL values ​​in SUM

I am currently working on some sql files, but a little confused.

I have this method that searches for cash transactions and withdraws cashback, but sometimes there are no cash transactions, so the value becomes NULL and you cannot subtract from NULL. I tried setting ISNULL around it, but it still turns to 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
+3
source share
3 answers

Maybe COALESCE()can help you?

You can try the following:

SUM(COALESCE(tp.payment_amount, 0))

or

COALESCE(SUM(tp.payment_amount), 0)

COALESCE(arg1, arg2, ..., argN) returns the first nonzero argument from the list.

+5
source

try setting ISNULL inside SUM and ABS, i.e. around the actual field for example

SUM(ISNULL(tp.payment_amount, 0))

SUM(ABS(ISNULL(tp.payment_amount, 0)))
+1
source

MS SQL , ISNULL SELECT? , ISNULL , ...

+1

More articles:


All Articles