SQL query: SUM(Quantity)-CodePudding

I'm trying to build a simple SQL query for an item availability check. My aim is to query the items whose total/summed quantity exceeds the stock level. This is what my table (simplified example) looks like:

LineNumber	Item	Quantity	StockQuantity
1	Banana	1	1
2	Apple	1	1
3	Strawberry	1	1
4	Banana	1	1

I have tried it with the following query, but it doesn't work:

SELECT 
    T1.[LineNumber], T1.[Item], 
    SUM(T1.[Quantity]), T1.[Stockquantity]
FROM 
    Table T1
WHERE 
    T1.[Quantity] > T1.[Stockquantity]
GROUP BY 
    T1.[LineNumber], T1.[Item], T1.[Quantity], T1.[Stockquantity]

Does anyone have any advice or tips for me on how I should do this query so that the total/summed quantities per line are compared with the stock quantity and not just the individual/single quantities per line with the stock quantity?

Thank you very much for your help in advance.

CodePudding user response：

Your example is possibly oversimplified, If StockQuantity is the result of a lookup into an inventory, so that in the example we can say the following:

Total stock on Hand of Bananas is 1

And then if you are expecting LineNumber 4 to be picked up because this line will make the SUM of Bananas greater than the total stock on hand of Bananas... then you need a Running Total of the Item records in this list:

WITH TotalsLookup as (
SELECT 
    T1.[LineNumber], T1.[Item], 
    T1.[Quantity], T1.[Stockquantity],
    SUM (T1.[Quantity]) OVER (PARTITION BY Item ORDER BY LineNumber) AS RunningTotal
FROM 
   [Table] T1
)
SELECT * 
FROM TotalsLookup
WHERE RunningTotal > Quantity;

LineNumber	Item	Quantity	Stockquantity	RunningTotal
4	Banana	1	1	2

See this Fiddle that covers a few variants of this query and will give you a playground to practice: http://sqlfiddle.com/#!18/12c386/3

The origin of StockQuantity can itself have a significant impact on the performance and other query options that might be viable, I call this out so that you can be mindful of this when you apply this logic into your application domain, if StockQuantity is not a static value, but is itself an aggregate then that may impact how the comparison should be performed.

CodePudding user response：

You can refer to aggregates of a GROUP BY in the having clause

SELECT 
    T1.[Item], 
    SUM(T1.[Quantity]) As TotalQuantity,
    MAX(T1.[Stockquantity]) AS StockLevel
FROM 
    Table T1
GROUP BY 
    T1.[Item]
HAVING
    SUM(T1.[Quantity]) > MAX(T1.[Stockquantity])

This assumes that the Stockquantity is the same for all occurrences of a product.

The WHERE clause is executed before grouping, the HAVING clause after grouping.

If you need the line numbers, then you can use the STRING_AGG aggregate function to join the line numbers into a single string per group

SELECT STRING_AGG(T1.[LineNumber], ', ') As LineNumbers, ...

CodePudding user response：

your SQL modified code(SQL code didn't work) that does not provide a single output

SELECT linenumber,
       a.item,
       quantity,
       SumQuantity,
       stockquantity
FROM   table A
       JOIN(SELECT item,
                   Sum(quantity) SumQuantity,
                   max(stockquantity) stockquantity
            FROM   table C
            GROUP  BY item) B
         ON A.item = B.item
WHERE  SumQuantity> stockquantity

ouput

linenumber	item	quantity	quantity2	stockquantity
1	Banana	1	2	1
4	Banana	1	2	1

However I use the follwing query

SELECT String_agg(linenumber, ',') LineNumbers,
       a.item,
       quantity,
       SumQuantity,
       stockquantity
FROM   table  A
       JOIN(SELECT item,
                   Sum(quantity) SumQuantity
                   max(stockquantity) stockquantity
            FROM   table  C
            GROUP  BY item) B
         ON A.item = B.item
WHERE  SumQuantity> stockquantity
GROUP  BY a.item,
          quantity,
          SumQuantity,
          stockquantity

that bring following result

LineNumbers	Item	Quantity	SumQuantity	StockQuantity
1,4	Banana	1	2	1

CodePudding user response：

Thank you all for your effort and suggestions for solutions. I have tried to incorporate them all. A few of them worked. With others I still have a few problems:

@Chris Schaller I like you solution a lot! But in my non-simplified, real SQL code, there are item groups that the PARTITION function cannot cope with. My result with your solution is therefore as follows. However, I will continue to work on it. Thanks!

WITH TotalsLookup as (
SELECT T1.[VisOrder] as 'VisOrder', T1.[U_position] as 'ItemGroup-LineNumber', T0.[DocNum] as 'DocNumber', T1.[ItemCode] as 'Itemcode', T1.[Quantity] as 'Quantity', T1.[OpenCreQty] as 'openQuantity', T2.[OnHand] as 'StockQuantity', SUM(T1.[Quantity]) OVER (PARTITION BY T1.[ItemCode] ORDER BY T1.[VisOrder]) AS RunningTotal

FROM OQUT T0  
INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] 

WHERE T0.[DocNum] = '332050' AND T2.[InvntItem] = 'Y'

)
SELECT * 
FROM TotalsLookup
WHERE RunningTotal > Quantity

Output: here

@RF1991 I have also tried to implement your solution, but it has not really worked so far. I am still looking for the mistake. Thanks for the help!

SELECT T1.[U_position], T1.[ItemCode], T1.[Quantity], T1.[OpenCreQty], T2.[OnHand] 
    
FROM OQUT T0  
INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] 
        
JOIN(SELECT T1.[ItemCode], SUM(T1.[Quantity]) as 'SumQuantity', MAX(T2.[OnHand]) as 'Stockquantity' FROM OQUT T0 INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]  GROUP BY T1.[ItemCode]) B 
        
ON T1.[ItemCode] = B.[ItemCode]
        
WHERE SumQuantity > stockquantity AND T0.[DocNum] = '332050' AND T2.[InvntItem] = 'Y'

@Oliver Jacot-Descombes It works. Thank you very much!