CODEWITHSUNDEEP
sqlsql-server
user3385784
user3385784

Reputation: 3

How to add two columns from two separate MS SQL queries with different number of rows

I have two Microsoft SQL queries both of which give two columns but different number of rows. These are the results:

First query:

ProductID, Inventory
1, 100
2, 50
3, 200

Second query:

ProductID, Sales
1, -20
2, -50

I want to get the below output:

ProductID, BalanceInventory
1, 80
2, 0
3, 200

I have tried using plus sign in the query like this:

Select t1.ProductID, 
       t1.Inventory + (Case when t2.Sales is null then 0 else t2.Sales end) as 'BalanceInventory' 
  from t1 full join t2 on t1.ProductID = t2.ProductID

The issue with this is that the DB structure is designed in such a way that Sales and Inventory cannot be run in the same query. So, I need to run two separate queries and then add the two columns Inventory and Sales for every ProductID.

The actual DB structure and query is much more complex. I have tried to simplify the problem by creating a hypothetical one.

Please help. This is eating up my head.

Thanks, Karan

Upvotes: 0

Views: 408

Answers (3)

Nick.Mc
Nick.Mc

Reputation: 19194

The other option is

SELECT UnionTable.ProductID, SUM(UnionTable.BalanceInventory) AS BalanceInventory
FROM (
    SELECT ProductID, Inventory As BalanceInventory
    FROM Table1
    UNION ALL
    SELECT ProductID, Sales As BalanceInventory
    FROM Table2
) As UnionTable
GROUP BY UnionTable.ProductID

To subtract instead of add, simply make one part negative:

SELECT UnionTable.ProductID, SUM(UnionTable.BalanceInventory) AS BalanceInventory
FROM (
    SELECT ProductID, Inventory As BalanceInventory
    FROM Table1
    UNION ALL
    SELECT ProductID, -Sales As BalanceInventory
    FROM Table2
) As UnionTable
GROUP BY UnionTable.ProductID

Upvotes: 1

Thiago Cardoso
Thiago Cardoso

Reputation: 90

You can avoid the case expression using the "isNull(expr, 0)".

About your problem, if you can't use any JOIN in your query, try to use UNION operator: http://technet.microsoft.com/en-us//library/ms180026.aspx

Upvotes: 0

Thit Lwin Oo
Thit Lwin Oo

Reputation: 3438

Try this

select ProductID, (inv.Inventory + s.Sales) as BalanceInventory
from
( 
    select  ProductID, Inventory
    from [table]
    where xxx
) inv
left outer join
(
   select  ProductID, Sales 
    from [table]
    where xxx
) s on (s.ProductID = inv.ProductID)

Upvotes: 1

Related Questions