How to run a nested subquery

Question

Hello.

For whom is familiar with AdventureWorks2019 Database, i am using the table productInventory.
I am trying to run a subquery into a query to get some values grouped by different fields.
basically i have a situation
where i have productID, Location ID, Quantity

for any product ID i have the other 2 ones.
e.g. for productID =2, i have 420 items in LocationID 1 but i have 318 items for locationid 6 for the same product, then i have
585 items for productID=3 for location 1 and so forth.
However, the total number of locations is 3 locationsID.
so far i just did those simple query with group by. i got the total values of quantities related to any locationID and to any productIDs
Those ones:

select sum(Production.ProductInventory.Quantity), LocationID FROM Production.ProductInventory GROUP BY LocationID
select sum(Production.ProductInventory.Quantity), ProductID FROM Production.ProductInventory GROUP BY productID

gives me the values, but i need more details
what i want to do it is

• to get, once having the productID , the subtotals of quantity for each locations.
• and opposite, once having the locationID, the subtotals of quantity for each productID.
  example:
  for productid=1 i have

LocationID =1 : 408
LocationID=2: 324
LocationID=3: 353

so in my idea i have
ProductID =1., LocationID=1 , Quantity (sum of 3 locations id quantities),
ProductID=1 LocationID =6 (sum etc..)
and so forth.

I hope it is clear. if not i will post more details.

Thanks in advance
Paolo

Answer 1

Hi @greenfield70 ,

Thank you so much for posting here.

Please refer below and check whether it is helpful to you.

--combine  
select ProductID, LocationID  
, sum(Quantity) OVER (PARTITION BY  LocationID)  SubtotalsLocationID  
, sum(Quantity) OVER (PARTITION BY  ProductID)  SubtotalsProductID  
 from Production.ProductInventory   
order by ProductID, LocationID  

--split  
select ProductID, LocationID  
, sum(Quantity) OVER (PARTITION BY  LocationID)  SubtotalsLocationID  
 from Production.ProductInventory   
order by ProductID, LocationID  
  
select LocationID,ProductID  
, sum(Quantity) OVER (PARTITION BY  ProductID)  SubtotalsProductID  
 from Production.ProductInventory   
order by LocationID,ProductID  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

Thanks to everyone for both Answers. The issue is solved.