CODEWITHSUNDEEP
sql-servergroup-bysumcase
Alan Pauley
Alan Pauley

Reputation: 121

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

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!

Upvotes: 3

Views: 989

Answers (3)

David Faber
David Faber

Reputation: 12485

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.

Upvotes: 2

Eray Balkanli
Eray Balkanli

Reputation: 7990

You can benefit from using cte like below. Please try:

;with cte (ref_x, qtyRemaining) as (
    Select ref_x, sum(qty) as qtyRemaining
    from table
    group by ref_x
)
select t.ref_x, t.qty, (case when t.qty > 0 then cte.qtyRemaining else 0 end)
from table t
inner join cte on t.ref_x = cte.ref_x

Upvotes: 0

Gustavo Topete
Gustavo Topete

Reputation: 1306

I think this is what you are looking for:

select ref_x, qty, sum(qty) over(partition by ref_x) as qtyRemaining
from table;

sum(qty) over(partition by ref_x) is what do the trick

Upvotes: 0

Related Questions