One SQL to summarize different 'group by' levels

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.

Solution

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