One SQL for summing different levels "by groups"

Question

I have a stock table in the database:

Based on the data in the Stock Table, I want to have more detailed data as an output:

Stock Qty represents the total stock in stock for item A.

Location Qty represents the total number of elements for element A for each location.

Sub Location Qty represents the total number of Item elements for a specific location for each additional location.

I was wondering if there is a way to get detailed output in a single query, or should I make each query individually and write code to combine between each result.

I could write:

• To get the Stock Qty value:

  SELECT Item, Sum(Qty) as StockQty
  From Stock
  Group By Item;
  

• To get Location Qty:

  SELECT Item, Location, Sum(Qty) as LocationQty
  From Stock
  Group By Item, Location;
  

• To get Sub Location Qty:

  SELECT Item, Location, SubLocation, Sum(Qty) as SubLocationQty
  From Stock
  Group By Item, Location, SubLocation;
  

datagridview.

, , datatable datagridview.

.

AS400, , , ( ) .

Answer 1

:

SELECT t3.Item, t3.StockQty,
       t2.Location, t2.LocationQty,
       t1.SubLocation, t1.SubLocationQty
FROM (
   SELECT Item, Location, SubLocation, Sum(Qty) as SubLocationQty
   From Stock
   Group By Item, Location, SubLocation) AS t1
JOIN (
   SELECT Item, Location, Sum(Qty) as LocationQty
   From Stock
   Group By Item, Location
) AS t2 ON t1.Item = t2.Item AND t1.Location = t2.Location
JOIN (
   SELECT Item, Sum(Qty) as StockQty
   From Stock
   Group By Item
) AS t3 ON t1.Item = t3.Item 

Answer 2

:

SELECT Item, Location, SubLocation, Sum(Qty) as SubLocationQty 
From Stock 
Group By Item, Location, SubLocation with rollup

rdbms, . "", "", .....

Answer 3

.

SELECT Item, 
(SELECT StockQty 
 FROM (SELECT Item, Sum(Qty) AS StockQty
       FROM Stock
       GROUP BY Item)) AS StockQty
(SELECT LocationQty 
 FROM (SELECT Item, Location, Sum(Qty) AS LocationQty
       FROM Stock
       GROUP BY Item, Location)) AS LocationQty,
(SELECT SubLocationQty 
 FROM (SELECT Item, Location, SubLocation, Sum(Qty) AS SubLocationQty
       FROM Stock
       GROUP BY Item, Location, SubLocation)) AS SubLocationQty
FROM Stock