Search code examples
sqlsql-serversql-server-2012

How to substract two tables SUM values in a SQL Server query?

I have two tables. I got the "SUM" from them. Now I need to subtract and get the value. How do I get this?

Table1

SELECT SUM(ItemHeader.InQty) AS NEW_BAL1, ItemID 
FROM ItemHeader  
GROUP BY ItemID

Table2

SELECT SUM(IssueDetail.Qty) AS NEW_BAL, ItemID 
FROM IssueDetail 
GROUP BY ItemID

Both are my tables.

Now I need to get subtract table1 value from table2 value.

Thank you

Solution

  • Does this work for you? You didn't state how you were presenting the end results (view, function, stored procedure) or if you need to reuse the summations later on in code. So the simplest is just to wrap each SELECT in parentheses and then do the math on those values in another SELECT

    SELECT 
    (SELECT SUM(IssueDetail.Qty) AS NEW_BAL, ItemID FROM IssueDetail GROUP BY ItemID)
    - (SELECT SUM(ItemHeader.InQty) AS NEW_BAL1, ItemID FROM ItemHeader GROUP BY ItemID) as [difference]