This discussion is archived
6 Replies Latest reply: Sep 16, 2013 11:58 AM by Greg.Spall RSS

query help

RajeshKanna Newbie
Currently Being Moderated

Hi guys,

 

My table contains both negative and positive values.The script is like below

{code}

 

CREATE TABLE sample(ID        NUMBER(5));

 

insert into sample values(1);

insert into sample values(2);

insert into sample values(3);

insert into sample values(-1);

insert into sample values(-2);

 

commit;

 

{code}

 

My Requirement is,I want the sum of both +ve and -ve values in two columns.The output

should be display as follows

 

posi_id  neg_id

-------  -------

  6         -3

 

Can anyone help me on this query?

  • 1. Re: query help
    Greg.Spall Expert
    Currently Being Moderated

    select sum(case when id > 0 then id else 0 end) pos_sum,

             sum(case when id < 0 then id else 0 end) neg_sum

    from sample;

  • 2. Re: query help
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Another way:

     

    SELECT  SUM (GREATEST (id, 0))  AS posi_id

    ,       SUM (LEAST    (id, 0))  AS neg_id

    FROM    sample

    ;

  • 3. Re: query help
    Greg.Spall Expert
    Currently Being Moderated

    FrankKulash wrote:

     

    Hi,

     

    Another way:

    I see your "another way" and raise you "an even other way"

     

    select sum(ABS(id)+id)/2 as pos_sum,

           sum(ABS(id)-id)/2 as neg_sum

      from sample;

     

    (but I think I'm losing in the performance department

     

    The real silly question will be ... how crazy a solution can you come up with for something like this   lol

  • 4. Re: query help
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    Your original idea (that is, "SUM (CASE ...") is the best general solution

    If we needed a sub-query anyway (which is often the case), then "SELECT ... PIVOT"  would be a good alternative:

     

    WITH got_id_sign AS

    (

        SELECT  id

        ,       SIGN (id)   AS id_sign

        FROM    sample

    )

    SELECT  *

    FROM    got_id_sign

    PIVOT   (    SUM (id)

            FOR  id_sign IN (  1   AS posi_id

                            , -1   AS neg_id

                            )

            )

    ;

    If the only reason for a sub-query is to get id_sign, however, then it's an awful lot of extra coding.

  • 5. Re: query help
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    If output on 2 separate rows (rather than 2 separate columns) is okay:

     

    SELECT    CASE  SIGN (id)

                  WHEN  1  THEN  'POSI_ID'

                  WHEN -1  THEN  'NEG_ID'

              END        AS pos_or_neg

    ,         SUM (id)   AS total

    FROM      sample

    GROUP BY  SIGN (id)

    ;

    Output:

     

    POS_OR_NEG      TOTAL

    ---------- ----------

    POSI_ID             6

    NEG_ID             -3

    Of course, the pos_or_neg column isn't really needed; you can tell which is which by the sign of the total.

  • 6. Re: query help
    Greg.Spall Expert
    Currently Being Moderated

    Neat, nevertheless

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points