turn positive values to negative in SQL Snowfalke

Question

I have a column where the values describe the price of an item that has been returned. They are positive and when sum:ing them I would need them to become negative. Ex:

order id	item id	returned	price	quantity
123	456	True	50	1
987	123	True	10	2

Example query below to get the sum of the returned value:

sum(case when returned = 'True' then (price * quantity) else 0 end) as returnedAmount

One thought I had was:

sum(case when returned = 'True' then (-1*(price * quantity)) else 0 end) as returnedAmount

But that returned null, not sure why. Does anyone have a smarter suggestion?

Your second code is actually fine – Radagast Mar 17 '22 at 16:28 — Radagast, Mar 17 '22 at 16:28

score 1 · Accepted Answer · answered Mar 17 '22 at 16:18

If the returned column is boolean then comparison is just column name:

SELECT col, 
  SUM(CASE WHEN retruned THEN -1*(price * quantity) ELSE 0 END) AS returnedAmmount 
FROM tab 
GROUP BY col;

If the query returns NULL it could mean that either PRICE or QUANTITY columsn are nullable for all values in a group:

SELECT col, 
  COALESCE(SUM(IIF(retruned, -1*(price * quantity),0)), 0) AS returnedAmmount 
FROM tab 
GROUP BY col;

score 1 · Answer 2 · answered Mar 17 '22 at 22:06

so you don't need to multiply by -1 you can just negate the value:

SELECT 
    order_id,
    sum(iff(returned,-(price * quantity), 0)) as returnedAmount
FROM VALUES
    (123,456,True,50,1),
    (987,123,True,10,2)
    t(order_id, item_id, returned, price,quantity)
GROUP BY 1
ORDER BY 1;

gives:

ORDER_ID	RETURNEDAMOUNT
123	-50
987	-20

So to the null, so ether value could null and as Lukasz showed, you can fix that on the outside of the sum, there are a few options ZEROIFNULL, COALESCE, NVL, IFNULL.

if you want the value zero, I feel zeroifnull is explicit, while the other three you have to parse the expression all the way to the right to see the alternative value.

SELECT 
    order_id,
    sum(iff(returned, -(price * quantity), 0)) as ret_a,
    zeroifnull(sum(iff(returned, -(price * quantity), 0))) as ret_b,
    coalesce(sum(iff(returned, -(price * quantity), 0)),0) as re_c,
    nvl(sum(iff(returned, -(price * quantity), 0)),0) as ret_d,
    ifnull(sum(iff(returned, -(price * quantity), 0)),0) as ret_e
FROM VALUES
    (123,456,True,50,1),
    (987,123,True,10,2),
    (988,123,True,null,2),
    (989,123,True,10,null),
    (989,123,True,null,null)
    t(order_id, item_id, returned, price,quantity)
GROUP BY 1
ORDER BY 1;

gives:

ORDER_ID	RET_A	RET_B	RET_C	RET_D	RET_E
123	-50	-50	-50	-50	-50
987	-20	-20	-20	-20	-20
988	null	0	0	0	0
989	null	0	0	0	0

turn positive values to negative in SQL Snowfalke

2 Answers2