How to get count of postitive and negative numbers per ID?

Question

I'd like to get a count of negative values and positive values for each id.

Sample Fiddle

ID=1 has 2 positive and 0 negative transactions. etc.

 with trans_detail as 
 (
 select 1 as trans_id, 100 as trans_amount  from dual union all
 select 1 as trans_id, 200 as trans_amount  from dual union all
 select 2 as trans_id, -100 as trans_amount  from dual union all
 select 2 as trans_id, -300 as trans_amount  from dual union all
 select 3 as trans_id, 400 as trans_amount   from dual union all
 select 3 as trans_id, -500 as trans_amount  from dual
 )

 select trans_id,
       count(*) over (partition by trans_id) as pos_count,
       count(*) over (partition by trans_id) as neg_count        
from trans_detail
where trans_amount > 0
UNION
select trans_id,
       count(*) over (partition by trans_id) as pos_count,
       count(*) over (partition by trans_id) as neg_count        
from trans_detail
where trans_amount < 0;

Desired Result:

 ID   POS_COUNT   NEG_COUNT
---- ----------- -----------
 1    2           0 
 2    0           2
 3    1           1

Answer 1

Count 1 each time you see a positive or negative amount and sum that up.

select trans_id,
sum(case when trans_amount >=0 then 1 else 0 end) as pos_amt,
sum(case when trans_amount < 0 then 1 else 0 end) as neg_amt
from trans_detail
group by trans_id

http://sqlfiddle.com/#!4/db410/12

Answer 2

select trans_id, 
nvl(sum(case when trans_amount < 0 then 1 end),0) as neg,
nvl(sum(case when trans_amount > 0 then 1 end),0) as pos
from trans_detail
group by trans_id

SQL Fiddle: http://sqlfiddle.com/#!4/db410/15

Answer 3

Try this

select trans_id,
       Sum(case when trans_amount>=0 then 1 else 0 end) as pos_count,
       Sum(case when trans_amount<0 then 1 else 0 end) as neg_count,
from trans_detail
group by trans_id

Answer 4

You can use a conditional count:

with trans_detail as 
 (
 select 1 as trans_id, 100 as trans_amount  from dual union all
 select 1 as trans_id, 200 as trans_amount  from dual union all
 select 2 as trans_id, -100 as trans_amount  from dual union all
 select 2 as trans_id, -300 as trans_amount  from dual union all
 select 3 as trans_id, 400 as trans_amount   from dual union all
 select 3 as trans_id, -500 as trans_amount  from dual
 )
select trans_id,
       count(case when trans_amount >= 0 then trans_id end) as pos_count,
       count(case when trans_amount < 0 then trans_id end) as neg_count        
from trans_detail
group by trans_id
order by trans_id;

  TRANS_ID  POS_COUNT  NEG_COUNT
---------- ---------- ----------
         1          2          0
         2          0          2
         3          1          1

Count ignores null values, so the implicit null 'else' to each case means those rows aren't counted. You can add else null if you prefer but it just makes it a bit longer. (I've included zero as 'positive' but you may want to ignore it completely as in your question; in which case just revert to > 0).

SQL Fiddle

You could also use the sign function, either in a case or a decode:

select trans_id,
       count(decode(sign(trans_amount), 1, trans_id)) as pos_count,
       count(decode(sign(trans_amount), -1, trans_id)) as neg_count        
from trans_detail
group by trans_id
order by trans_id;

SQL Fiddle; this ignores zero but you could include it in either decode if you wanted to.