Search code examples
sqlsql-serverddp

How to subtract 2 SUM in SQL query

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.

Solution

  • 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