CODEWITHSUNDEEP
sqldatabasedb2
NotADeveloper
NotADeveloper

Reputation: 3

DB2 Select from two tables when one table requires sum

In a DB2 Database, I want to do the following simple mathematics using a SQL query:

AvailableStock = SupplyStock - DemandStock

SupplyStock is stored in 1 table in 1 row, let's call this table the Supply table. So the Supply table has this data:

ProductID | SupplyStock
---------------------
109            10
244             7  edit: exclude this product from the search

DemandStock is stored in a separate table Demand, where demand is logged as each customer logs demand during a customer order journey. Example data from the Demand table:

ProductID | DemandStock 
------------------------
109            1
244            4  edit: exclude this product
109            6
109            2

So in our heads, if I want to calculate the AvailableStock for product '109', Supply is 10, Demand for product 109 totals to 9, and so Available stock is 1.

How do I do this in one select query in DB2 SQL?

The knowledge I have so far of some of the imagined steps in PseudoCode:

The results will look like this:

Product ID | AvailableStock
109                9

I'd love to get this selected in one SQL select query.

Edit: I've since received an answer (that was almost perfect) and realised the question missed out some information. This information: We need to exclude data from products we don't want to select data for, and we also need to specifically select product 109. My apologies, this was omitted from the original question. I've since added a 'where' to select the product and this works for me. But for future sake, perhaps the answer should include this information too.

Upvotes: 0

Views: 991

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You do this using a join to bring the tables together and group by to aggregate the results of the join:

select s.ProductId, s.SupplyStock, sum(d.DemandStock),
       (s.SupplyStock - sum(d.DemandStock)) as Available
from Supply s left join
     Demand d
     on s.ProductId = d.ProductId 
where s.ProductId = 109
group by s.ProductId, s.SupplyStock;

Upvotes: 1

Related Questions