question

mykonos avatar image
mykonos asked

Converting negative to positive value in SQL

Hello everyone.

I am trying to do a SQL query on a sales table with different sales types.

Sales type S is SALE, M is EXCHANGE, R is RETURN, K is ROUNDING.

Return and Rounding are both negative value in database.

I would only like to convert sales type 'K' to positive value.

I read about using ABS function but i am not able to get it to work.

Something like

Select

ABS(amount) where Salestype='K' as Rounding, amount where Salestype='S' as Sale,

amount where Salestype='R' as Return, amount where Salestype='M' as Exchange

from Retailtable

sql-server-2008
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

You need a CASE statement. I'm not sure of your underlying table, or how you want the results, but this should give you an idea

declare @YourTable table (Salestype char(1), amount int)

insert into @YourTable (Salestype, amount) select 'S', 100
insert into @YourTable (Salestype, amount) select 'K', 10
insert into @YourTable (Salestype, amount) select 'K', -25
insert into @YourTable (Salestype, amount) select 'R', 30
insert into @YourTable (Salestype, amount) select 'M', 5

select
	salestype,
	case when Salestype='S' then amount else 0 end as Sales,
	case when Salestype='M' then amount else 0 end as Exchange,
	case when Salestype='R' then amount else 0 end as 'Return',
	case when Salestype='K' then abs(amount) else 0 end as Rounding
from @YourTable
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.