CODEWITHSUNDEEP
sqlsybase
Mike Sickler
Mike Sickler

Reputation: 34461

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

Upvotes: 19

Views: 67531

Answers (4)

schinazi
schinazi

Reputation: 858

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

Upvotes: 42

Ayyappan Anbalagan
Ayyappan Anbalagan

Reputation: 11312

select (select JV_GroupsHead.GroupTitle
         from JV_GroupsHead
         whereJV_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

Upvotes: 0

Mihir Kale
Mihir Kale

Reputation: 1

I found a new answer to this problem using the DECODE function. I hope this turns out to be useful for everyone.

select activity_dt, 
sum((DECODE(activity_amt /-ABS(activity_amt), 1, activity_amt, 0))) as credits,
sum((DECODE(activity_amt /-ABS(activity_amt), -1, activity_amt, 0))) as debits
from the_table
group by activity_dt
order by activity_dt;

Upvotes: 0

Guffa
Guffa

Reputation: 700720

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

Upvotes: 5

Related Questions