SQL SUM(QTY) but only for records where QTY > 0

Question

Edit: how would one handle this when there is another criteria (see below, added code) and qtyRemaining = SUM(qty) when code IN (25, 26) but NOT 27.

I have a series of records, some where qty < 0, others where qty > 0. I have fields 1 and 2 below, my goal is to get field 3:

ref_x         qty     code      qtyRemaining(goal) 
--------------------------------------------------
TKAB030AA      0      25            0
TKAB030AA      0      25            0 
TKAB030AA     -3      26            0
TKAB030AA    -197     26            0
TKAB030AA      3      27          -200
TKAB030AA     197     27          -200

The logic is, if qty < 0, then qtyRemaining should be 0, but if qty > 0, SUM(qty where trcode IN(25, 26)) (even records where qty < 0) grouping by ref_x.

The problem I'm running into is that I cannot do:

CASE 
   WHEN qty < 0 
      THEN 0 
      ELSE SUM(QTY) 
END [QtyRemaining]

...because SQL wants me to put qty in a group by since it's outside of the SUM() function. but if I do:

, SUM(CASE WHEN qty < 0 THEN 0 ELSE QTY END) [QtyRemaining]

...this isn't what I want either. Is there some way to do this that I'm just not seeing? Please help!

Answer 1

I think your best bet is to use a window function here. Aggregation is not really what you're after, since you seem from your question to want all rows to show:

SELECT ref_x, qty
     , CASE WHEN qty <= 0 THEN 0 ELSE SUM(qty) OVER ( PARTITION BY ref_x ) END AS [QtyRemaining]
  FROM mytable;

You could get the same answer by using aggregation and joining the table to itself, but that's a lot more typing:

WITH t2 AS (
    SELECT ref_x, SUM(qty) AS total_qty
     GROUP BY ref_x
)
SELECT t1.ref_x, t1.qty
     , CASE WHEN t1.qty <= 0 THEN 0 ELSE t2.total_qty END AS [QtyRemaining]
  FROM t1 INNER JOIN t2
    ON t1.ref_x = t2.ref_x;

EDIT FOR NEW CONDITION:

I think you'll want this:

SELECT ref_x, qty
     , CASE WHEN qty <= 0 THEN 0 ELSE SUM(CASE WHEN code IN (25,26) THEN qty ELSE 0 END) OVER ( PARTITION BY ref_x ) END AS [QtyRemaining]
  FROM mytable;

Hope this helps.