CODEWITHSUNDEEP
sql-server
John Bergqvist
John Bergqvist

Reputation: 1003

SQL Server - Struggling to group/sum values across multiple related tables

I'm running this query: 

SELECT 
    g.PartNum,
    g.Supplier,
    (g.InitialQuantityToInventory + SUM(COALESCE(t.AmountInventoryAdjusted + t.AmountReturned, 0))) AS StockLevel
FROM 
    GoodsIn g
LEFT JOIN 
    Transfers t ON g.GoodsInNumber = t.GoodsInNumber
WHERE 
    g.PartNum = '123'
GROUP BY 
    g.PartNum, g.Supplier, g.InitialQuantityToInventory

And it's returns these results: 

123,SUP1,67
123,NULL,18
123,NULL,0
123,NULL,45
123,NULL,0

However I would like the StockLevel (in the 3rd column) to SUM on the supplier name, even when it's null, so that my expected result should be:

123,SUP1,67
123,NULL,63

What am I doing wrong? The query should (across all GoodsIn Numbers that have the same PartNumber & Supplier) Sum the InitialQuantities and their Amounts Adjusted & Returned for each transfer associated with the GoodsIn record.

This is the data for that part & supplier in the GoodsIn Table:

GINum  Part Num    Supplier InitialQuantityToInventory
73367   123        NULL     81    
73570   123        NULL     18
74154   123        NULL     320
74835   123        Sup1     0
74836   123        NULL     500
75738   123        Sup1     0

And this is the corresponding rows from the Transfers table (T being short for TransferNum):

GINum      T    Adj    Ret
73367      1    -81    0
74154      1    -200   0
74154      2    -120   45
74835      1    67     0
74836      1    -500   0
75738      1    -300   0
75738      2    300    0

Upvotes: 0

Views: 62

Answers (3)

Steve Ford
Steve Ford

Reputation: 7763

Try this, group the Transfers before joining and grouping

SELECT
    g.PartNum,
    g.Supplier,
    SUM(g.InitialQuantityToInventory + COALESCE(t.adjRet, 0))
FROM Goodsin g
LEFT JOIN 
    (
        SELECT GINum, SUM(AmountInventoryAdjusted + AmountReturned) AS adjRet
        FROM Transfers
        GROUP BY GINum
    ) T
    ON t.GINum = g.GiNum
GROUP BY g.PartNum,    g.Supplier

Upvotes: 0

Jamie Burns
Jamie Burns

Reputation: 1318

Do you need to not group by InitialQuantityToInventory?

;WITH Logs (Supplier, Initial, StockChange)
AS
(
SELECT
    g.Supplier,
    g.InitialQuantityToInventory,
    (SUM(COALESCE(t.AmountInventoryAdjusted  + t.AmountReturned,0))) AS StockChange
FROM
    GoodsIn g
LEFT JOIN
    Transfers t ON g.GoodsInNumber = t.GoodsId
GROUP BY
    g.Supplier, g.InitialQuantityToInventory
)

SELECT 
    Supplier,
    SUM(Initial) + SUM(StockChange) AS StockLevel
 FROM
    Logs
GROUP BY
    Supplier

What might be happening is you're getting a row per unique InitialQuantityToInventory, but from what I gather, you want these to be summed, right?

This could probably be optimised further (and probably doesn't need a CTE), but hopefully it at least returns the data you expect.

Upvotes: 1

Chaos Legion
Chaos Legion

Reputation: 2970

I don't think you need to GROUP BY InitialQuantityToInventory. Try following query and let me know if this works for what you need.

SELECT
    g.PartNum,
    g.Supplier,
    MAX(g.InitialQuantityToInventory) + SUM(COALESCE(t.AmountInventoryAdjusted + t.AmountReturned,0)) AS StockLevel
FROM
    GoodsIn g
LEFT JOIN
    Transfers t ON g.GoodsInNumber = t.GoodsInNumber
WHERE
    g.PartNum = '123'
GROUP BY
    g.PartNum, g.Supplier

Upvotes: 0

Related Questions