Where clause in an OVER(order by) statement?

Question

I have a temporary table that gets populated with various products like this:

    DECLARE @temp TABLE (ID int, productCode int, quantity int, Available bit, RunningTotal int)
    INSERT INTO @temp (ID, productCode, quantity, Available, RunningTotal)
    SELECT
        item.ID
        , item.ProductCode
        , item.Quantity
        , item.available
        , SUM(item.Quantity) OVER (Order BY item.productCode ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    FROM dbo.item

Which results in this kinda table:

LineID  | ProductCode |  Quantity  | Available | RunningTotal
001     |    00001    |    2       |    3      |     null
002     |    00002    |    2       |    3      |     2      
003     |    00001    |    2       |    3      |     4   

The issue I have right now is that I want the running total to only count up matching product IDs, but it's adding every product. The table should look like this:

LineID  | ProductCode |  Quantity  | Available | RunningTotal
001     |    00001    |    2       |    3      |     null
002     |    00002    |    2       |    3      |     null      
003     |    00001    |    2       |    3      |     2   

But I can't seem to figure out how to change this line to only group by productID?

SUM(item.Quantity) OVER (Order BY item.productCode ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

Changing to Group By prevents me from using the ROWS BETWEEN clause. I'm not looking to SUM distinct item.quantity values, but I can't change that to distinct item.productCode values because I need to sum up the quantities. I'm not sure what syntax I could use for a WHERE Condition after the Order By, but I'm thinking that could be somewhere to start?

Answer 1

Try to partition by ProductID, like this:

SUM(item.Quantity) OVER (PARTITION BY ProductID, Order BY item.productCode ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)