One SQL to summarize different 'group by' levels

Question

I have a Stock table in database:

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

Stock Qty represents the total qty in stock for Item A.

Location Qty represents the total qty for Item A for each Location

Sub Location Qty represents the total qty for Item A, for a specific location for each sub Location.

I was wondering if there is a way to get the detailed output in one query or should I make each query individually and write some code to join between each results.

I could write:

• In order to get Stock Qty:

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

• In order to get Location Qty:

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

• In order to get Sub Location Qty:

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

And then in code behind, write some code for output in a datagridview.

Actually, I am looking for a query that returns all the data in one datatable and bind it to the datagridview.

Any other solution is welcome.

I am working in AS400 but if there is a solution in others DBMS, please answered, I will try to make it work (if possible) in my DBMS environment.

Answer 1

Try this:

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

how about :

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

if you have rollup in your rdbms it is really simple. just replace the 'null' you get with the string 'Total' and you're homefree.....