How to subtract 2 SUM in SQL query

Question

I want to find the total outstanding amount for a particular item amount excluding the refund amount.

There will be two columns for each item based on number of times it was sold, final_amount and 2nd refund_amount for each items and I want to subtract total refund_amount from final_amount for each item.

PFB the code/query

SELECT item_id,
       SUM(final_amount) as total_amount,
       SUM(ISNULL(refund_amount, 0)) AS total_refund
       SUM(final_amount) - SUM(ISNULL(refund_amount, 0)) AS outstanding_amount                    
FROM tabel1 
WHERE item_id in ('119688521',
                  '109536343',
                  '99459466',
                  '97126817',
                  '138148320',
                  '107816131')
GROUP BY 1

I am getting a syntax error for "SUM" near

SUM(final_amount)-SUM(ISNULL(refund_amount, 0)) AS outstanding_amount

I tried different code:

SUM(total_amount - total_refund) AS npv

And I got the same error.

Answer 1

First off, there's a few errors in syntax. You're missing a comma between all the select-elements (between your total_refund and outstanding_amount). You should also check if the SUM() was null, not if the column was null. You can also use COALESCE() if you prefer that. Finally, you need to GROUP BY something useful, like the item_id.

SELECT item_id,
       SUM(final_amount) as total_amount,
       ISNULL(SUM(refund_amount), 0) AS total_refund,
       SUM(final_amount) - ISNULL(SUM(refund_amount), 0) AS outstanding_amount
FROM tabel1 WHERE item_id in ('119688521',
                              '109536343',
                              '99459466',
                              '97126817',
                              '138148320',
                               '107816131')

GROUP BY item_id

• Live demo at http://www.sqlfiddle.com/#!18/94f518/5

Answer 2

You are missing a comma. In addition, you should check for NULL after the SUM():

SELECT item_id,
       SUM(final_amount) as total_amount,
       COALESCE(SUM(refund_amount), 0) AS total_refund,
       (SUM(final_amount) - COALESCE(SUM(refund_amount), 0)
       )  AS outstanding_amount
FROM tabel1
WHERE item_id in ('119688521', '109536343', '99459466', '97126817', '138148320', '107816131')
GROUP BY 1

Answer 3

Try this

SELECT item_id,
SUM(final_amount) as total_amount,
SUM(ISNULL(refund_amount, 0)) AS total_refund

SUM(final_amount - isnull(refund_amount, 0)) AS outstanding_amount

FROM tabel1 WHERE item_id in ('119688521',
'109536343',
'99459466',
'97126817',
'138148320',
'107816131')

GROUP BY item_id

Answer 4

Your are missing to put a comma (,) after your column ".... AS total_refund". Add the comma and this will resolve the syntax issue.

And please use GROUP BY item_id instead of GROUP BY 1