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

Question

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 1

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

Answer 2

I'm not sure about the exact syntax in Sybase, but you should be able to group on the date and sum up the positive and negative values:

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
  theTable
group by
  activity_dt

Answer 3

select (select JV_GroupsHead.GroupTitle from JV_GroupsHead where
JV_GroupsHead.Id=jv.GroupId) as 'GroupName',jv.Revenue ,jv.AccountNo,jv.AccountNoTitle,
(case when jv.Revenue < 0 then jv.Revenue else 0 end) as 'debits', (case when jv.Revenue> 0 then jv.Revenue else 0 end) as 'credits' from JVFunction1('2010-07-08','2010-08-08') as jv