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?

Answer 1

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;