CODEWITHSUNDEEP
snowflake-cloud-data-platformnegative-integer
Sevgi Camuz
Sevgi Camuz

Reputation: 95

turn positive values to negative in SQL Snowfalke

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?

Upvotes: 1

Views: 2702

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

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;

Upvotes: 1

Related Questions