6 Replies Latest reply: Sep 16, 2013 1:58 PM by Greg Spall RSS

    query help


      Hi guys,


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



      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);






      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

          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



            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

              FrankKulash wrote:




              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



                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



                  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)




                  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

                    Neat, nevertheless