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