Thursday, November 17, 2011

Counting Negative and Postive Numbers in Table Row


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: