How to add a query to a table in SQL?

I have 3 tables. For simplicity I changed them to these sample tables.

table1: CorporateActionSmmary

RATE  Quantity  ProductID
--------------------------
56        0        1487
30        0        1871
40        0        8750

table2# ProductMaster

RATEGROSS  ISIN      ProductID
--------------------------
60         JP0001     1487
33         JP0002     1871
45         JP0003     8750

table3# OpenPosition

Quantity        ProductID
-------------------
5              1487
1              1487
5              1487
3              1871
2              1871
4              8750
2              8750
7              8750
3              8750

First I need to add ISIN from table2 to table1 table1: CorporateActionSmmary

RATE  Quantity  ProductID      ISIN
-------------------------------------
56        0        1487       JP0001 
30        0        1871       JP0002 
40        0        8750       JP0003 

So, I used this code

SELECT [dbo].[CorporateActionSummary].*, [dbo].[ProductMaster].[ISIN]
FROM [dbo].[CorporateActionSummary] JOIN [dbo].[ProductMaster] ON CorporateActionSummary.ProductID = ProductMaster.ProductID

Now as you can see the Quantity is missing in Table1 so I have to add-up all the quantities in Table3 for each product ID and add to Table1(as a new column or over-write the Quntity column)

I think I can get the sum of each ProductID’s Quantity by the following code, But how can I add it to Table1 that already has ISIN column

SELECT SUM(Qantity),ProductID
FROM [dbo].[OpenPositions]

I am super new to SQL, please explain in detail if it is possible, thank you

I am using Microsoft SQL Server Management Studio

Answer

you are almost there.. you just need to use the same logic to join to the product master table. However, since you need the total of quantity, you need to group by the other columns you select (but not aggregate).

The query will be something like this :

SELECT 
[dbo].[CorporateActionSummary].ProductID
, [dbo].[ProductMaster].[ISIN]
,sum([OpenPosition].Quantity) as quantity

FROM [dbo].[CorporateActionSummary] 

JOIN [dbo].[ProductMaster] 
ON CorporateActionSummary.ProductID = ProductMaster.ProductID

JOIN [dbo].[OpenPosition] 
ON CorporateActionSummary.ProductID = OpenPosition.ProductID


group by 

[dbo].[CorporateActionSummary].ProductID
, [dbo].[ProductMaster].[ISIN]

if you want to add more columns to your select, then you need to group by those colums as well