Mike Sickler Mike Sickler - 4 months ago 12
SQL Question

How to separate positive and negative numbers into their own columns?

I have a table with the following columns and data:

activity_dt | activity_amt
2009-01-01 | -500
2009-01-01 | 750


Can I write a query that looks at the sign of
activity_amt
and puts it in the credits column if it's positive, and the debits column if it's negative? (I'm using Sybase)

activity_dt | debits | credits
2009-01-01 | -500 | 750

Answer
select activity_dt, sum(case when activity_amt < 0 then activity_amt else 0 end) as debits, sum(case when activity_amt > 0 then activity_amt else 0 end) as credits
from the_table
group by activity_dt
order by activity_dt