SQL Server select records upto a running total (running sum)

vs3odd8k 于 2024-01-05 发布在其他

关注(0)|答案(1)|浏览(94)

I have a table similar to this in my Azure SQL Database
| itemId | qty |
| ------------ | ------------ |
| 1 | 19 |
| 2 | 22 |
| 3 | 21 |
| 4 | 113 |
| 5 | 8 |
| 6 | 25 |

I have one input value X (E.g. X = 20)

Now all I want is to write a select statement to select all the itemIds of which the total qty should be >= X

So, if X=20, Item 1 and 2 should be selected, because sum(qty) will be 41 which is > 20 similarly if X = 50, then item 1, 2 and 3 should be selected, and if we do not have that much qty (X=500) then all records should be selected,

Sequence does not matter, example ,for X=20 valid answers are

1. item 1, item 2 (total sum = 41)
 2. item 2 only (total sum = 22)
 3. item 3 only (total sum = 21)
 4. item 4 only (total sum = 113)
 5. item 6 only (total sum = 25)

sql-server

来源：https://stackoverflow.com/questions/77752132/select-records-upto-a-running-total-running-sum

1条答案

按热度按时间

dxxyhpgq1#

Seems like you could use a windowed SUM here to get the running total up to the prior row and then return rows where that total is less than your threshold:

DECLARE @Threshold int = 20;

WITH CTE AS(
    SELECT V.ItemID,
           V.Quantity,
           ISNULL(SUM(V.Quantity) OVER (ORDER BY V.ItemID
                                        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS PriorTotal
    FROM (VALUES(1,19),
                (2,22),
                (3,21),
                (4,113),
                (5,8),
                (6,25)) V(ItemID,Quantity))
SELECT ItemID,
       Quantity
FROM CTE
WHERE CTE.PriorTotal < @Threshold;

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server select records upto a running total (running sum)

1条答案

相关问题

热门标签

最新问答