Geek Answers Handbook

How to get the number of positive and negative numbers per identifier?

I would like to get the number of negative values ​​and positive values ​​for each id.

Script example

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
+4
source share
4 answers

Quantity 1 every time you see a positive or negative quantity and sum it 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

+4
source 
 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

+1
source

You can use a conditional number:

 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' for each case means that these lines are not counted. You can add else null if you want, but that just makes it a little longer. (I included zero as "positive", but you can completely ignore it, as in my question, in which case just go back to > 0 ).

SQL Fiddle

You can also use the sign function either in the case or in the 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 it ignores zero, but you can include it in decoding if you want.

+1
source

try it

 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
+1
source

More articles:


All Articles