You have a result set with positive, negative and neutral values like this:
select * from result;
KEY VAL
---------- ----------
first -1.222
second -.03
third -.02
fourth 0
fifth 1.2
sixth .03
Now, you want to count how many negative, postitive and neutral values you have.
3 negative
1 no change
2 positive
Solution:
Use the Oracle SIGN ( n ) function which returns -1 if n < 0. If n = 0, then the function returns 0. If n > 0, then SIGN returns 1.
create table result (
key varchar2(10),
val number
);
insert into result (key,val) values ('first',-1.222);
insert into result (key,val) values ('second',-.03);
insert into result (key,val) values ('third',-.02);
insert into result (key,val) values ('fourth',0);
insert into result (key,val) values ('fifth',1.2);
insert into result (key,val) values ('sixth',.03);
select count(decode(sign(val),-1,-1)) neg,
count(decode(sign(val),0,0)) zero,
count(decode(sign(val),1,1)) pos
from result;
NEG ZERO POS
---------- ---------- ----------
3 1 2
No comments:
Post a Comment