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

Most accounting systems avoid negative numbers in the database. They have two fields: amount which is always positive and a flag field for whether the transaction is a debit or credit. — jmucchiello, Nov 09 '09 at 02:42
nice question n solved me a lot of problems. just in passing did u notice that when u add a positive percentage to a negative percentage the net doesnt square off... if an asset goes up 100% and goes down 50% they are at the same price point.. i hope u get what i mean.. so it kinda calls for a case statement for adding negative numbers to positive numbers and hence one more reason to always stay positive !!!! — Raghavan, Jul 05 '13 at 11:35

score 41 · Accepted Answer · edited Sep 10 '16 at 18:48

41

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

edited Sep 10 '16 at 18:48

Floern

33,559
24
104
119

answered Nov 09 '09 at 02:33

schinazi

848
8
6

Thanks! +1 for being quick on the draw. – Mike Sickler Nov 09 '09 at 02:38

score 5 · Answer 2 · answered Nov 09 '09 at 02:33

5

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

answered Nov 09 '09 at 02:33

Guffa

687,336
108
737
1,005

1

For symmetry, you could use '<' and '>' without any trauma - the zeroes will be counted as zeroes... – Jonathan Leffler Nov 09 '09 at 04:17
@Jonathan: Yes, in this case. I make an active choise to use the complementing operators so that all records are included, in case this example would be used for something slightly different. :) – Guffa Nov 09 '09 at 07:32
thanks jon ! ya but how r u so sure that if u leave the 0 out the record will be counted, in this case it makes sense cos u r just adding up a 0 but then this simple select could could actually involve a variation from a mean as in sum(case when activity_amt >0 then activity_amt = activity_amt - MEAN_OF_A_SET_OF_NUMBERS else ....... symmetrically i wud do case when amt>0, case when amt<0 and case when amt=0 and case when amt is NULL.. it always makes sense to first take the boundary conditions out of the equation, little more code, little more clocks but u get home with the shirt ! – Raghavan Jul 05 '13 at 11:38
@user1974729: You can sum up more things if you like, but the OP only asked about the positive and negative amounts. – Guffa Jul 05 '13 at 12:46
full credit to u for pointing out that 0 will be counted and NULL wont be counted at all. it takes a lot of experience to figure that out.im forever confused with the boundary conditions of 0,NULL cos they always come back to hurt me and so i recommended the usage of the 0 statement seperately for users like me and not advanced users like u who have it covered.. – Raghavan Jul 05 '13 at 18:31

score 0 · Answer 3 · edited Jul 27 '13 at 23:22

0

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;

edited Jul 27 '13 at 23:22

eggyal

122,705
18
212
237

answered Jul 27 '13 at 23:04

Mihir Kale

1
1

score 0 · Answer 4 · edited Jan 08 '15 at 09:29

0

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

edited Jan 08 '15 at 09:29

Igor Borisenko

3,806
3
34
49

answered Sep 21 '10 at 10:40

Ayyappan Anbalagan

11,022
17
64
94

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

4 Answers4