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

Question

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

Your current query look fine to me, it should work as it is. Now how complex your actual query is? without looking at the actual query nothing can be said. — M.Ali
– M.Ali, Commented Mar 5, 2014 at 23:07

Thit Lwin Oo · Accepted Answer · 2014-03-05 23:02:32Z

1

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)

answered Mar 5, 2014 at 23:02

Thit Lwin Oo

3,4283 gold badges22 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3385784 Over a year ago

Thanks Thit Lwin Oo. This works with a little modification on the IsNull part, which was suggested by Thiago Cardoso. I used this code: select ProductID, (inv.Inventory + isnull(s.Sales,0)) 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)

Nick.Mc Over a year ago

Just be aware that if there is one or more sales records but no inventory record for a product, this solution will ignore the product entirely. i.e. it won't include the sales at all.

user3385784 Over a year ago

Thanks ElectricLlama.. I figured that out.. But, in my case, the Sales records are always a sub-set of Inventory records.. Thanks for the caution though..

Nick.Mc · Accepted Answer · 2014-03-05 23:50:02Z

1

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

edited Mar 5, 2014 at 23:50

answered Mar 5, 2014 at 23:09

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

3 Comments

user3385784 Over a year ago

Thanks ElectricLlama. This solution also works just fine. I tried both the solutions and got the same result. Just one question, in case we have to subtract the two values, how do we replace the SUM part?

Thit Lwin Oo Over a year ago

Use my sql script. just change (inv.Inventory - s.Sales)

user3385784 Over a year ago

Yes Thit Lwin Oo. I did that and got the results. Was just trying to know how to modify the query given by ElectricLlama to subtract values instead of adding them. Anyways, thanks a ton to all of you. I guess I should have posted this query an hour back. Could have saved some time. Cheers.!!

Thiago Cardoso · Accepted Answer · 2014-03-05 23:04:36Z

0

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

answered Mar 5, 2014 at 23:04

Thiago Cardoso

903 bronze badges

1 Comment

user3385784 Over a year ago

In all my queries, i was using the case expression to avoid null values. I didn't know about IsNull. Thanks Thiago. This will help me a lot. Cheers.!!

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related