7 Replies Latest reply on Nov 6, 2007 5:17 PM by lmconsite

    Sum over group

    lmconsite
      <p>Hello,<br>
      <br>
      I have a table <font face="Courier New">t1</font> with the following data:<br>
      <br><font face="Courier New">ID_NUMBER VALUE1<br>1              0<br>2              4<br>3              4<br>4             -2<br>5              4<br>6              6<br>7             -3<br>8             -2<br>9             -2<br>10             2<br>
      11             4</font><br>
      <br>I would like to show an accumulative sum of negative value1 only, restarting
      the sum on every new negative group, ordered by id_number. So far, when I use the
      <font face="Courier New">DECODE(SIGN(value1), -1, SUM(DECODE(SIGN(value1), -1,
      value1, 0)) OVER(ORDER BY id_number)) </font>function, the accumulative sum
      considers all the negative values:</p>
      <p><font face="Courier New">ID_NUMBER VALUE1 MY_SUM<br>1         0     
      <br>2         4     
      <br>3         4     
      <br>4        -2         
      -2<br>5         4     
      <br>6         6     
      <br>7        -3         
      -5<br>8        -2         
      -7<br>9        -2         
      -9<br>10        2     
      <br>
      11        4      <br>
      </font><br>However, I would to show my sum like this:</p>
      <p><font face="Courier New">ID_NUMBER VALUE1 MY_SUM<br>1         0     
      <br>2         4     
      <br>3         4     
      <br>4        -2         
      -2<br>5         4     
      <br>6         6     
      <br>7        -3         
      -3<br>8        -2         
      -5<br>9        -2         
      -7<br>10        2     
      <br>
      11        4     
      </font><br><br>Is there a way to accomplish this using analytic functions only?<br>
      <br>Thanks in advance,<br><br>Luis</p>
        • 1. Re: Sum over group
          584412
          Hi, you need to use partition by:
          select id_number, value1, DECODE(SIGN(value1), -1, SUM(DECODE(SIGN(value1), -1, value1, 0)) OVER(partition by DECODE(SIGN(value1),-1, 1,0) ORDER BY id_number))
          from t1
          order by 1
          /
          • 2. Re: Sum over group
            lmconsite
            <p><font face="Arial">JS1,<br>
            <br>
            The function you gave me is accumulating all negatives, however I wish to reset
            the sum on every new set of negative values.</font><font face="Courier New"><br>
             </font></p>
            <p><font face="Courier New">ID_NUMBER VALUE1 JS1_SUM SUPPOSED_SUM<br>
            1             
            0       <br>
            2             
            4 <br>
            3             
            4 <br>
            4             -2     
            -2           -2<br>
            5             
            4 <br>
            6             
            6 <br>
            7             -3     
            -5           -3<br>
            8             -2     
            -7           -5<br>
            9             -2     
            -9           -7<br>
            10             2 <br>
            11             4
            </font></p>
            <p><font face="Arial">Thanks,</font></p>
            <p><font face="Arial">Luis</font></p>
            • 3. Re: Sum over group
              170207
              SQL> with t1 as (
                2    select 1 as ID_NUMBER, 0 as VALUE1 from dual union all
                3    select 2, 4 from dual union all
                4    select 3, 4 from dual union all
                5    select 4, -2 from dual union all
                6    select 5, 4 from dual union all
                7    select 6, 6 from dual union all
                8    select 7, -3 from dual union all
                9    select 8, -2 from dual union all
               10    select 9, -2 from dual union all
               11    select 10, 2 from dual union all
               12    select 11, 4 from dual
               13  ),
               14  Range_Start_End AS (
               15    select Id_Number, Is_Range_Start, Is_Range_End
               16    from (
               17      select t1.Id_Number,
               18        case
               19          when lag(Id_Number) over (order by Id_Number) = Id_Number - 1 then 'N'
               20          else 'Y'
               21        end as Is_Range_Start,
               22        case
               23          when lead(Id_Number) over (order by Id_Number) = Id_Number + 1 then 'N'
               24          else 'Y'
               25        end as Is_Range_End
               26      from t1
               27      where Value1 < 0
               28    )
               29    where Is_Range_Start = 'Y'
               30      or Is_Range_End = 'Y'
               31  ),
               32  Range_Bounds AS (
               33    select Id_Number as Range_Start,
               34      case
               35        when Is_Range_End = 'Y' then Id_Number
               36        else Next_Id_Number
               37      end as Range_End
               38    from (
               39      select Id_Number, Is_Range_Start, Is_Range_End,
               40        lead(Id_Number) over(order by Id_Number) as Next_Id_Number
               41      from Range_Start_End
               42    )
               43    where Is_Range_Start = 'Y'
               44  )
               45  select t1.Id_Number, t1.Value1,
               46    case
               47      when b.Range_Start is not null then
               48        sum(t1.Value1) over(
               49          partition by b.Range_Start
               50          order by t1.Id_Number
               51          rows unbounded preceding
               52        )
               53      else null
               54    end as Total
               55  from t1
               56    left outer join Range_Bounds b on (t1.Id_Number between b.Range_Start and b.Range_End)
               57  order by t1.Id_Number
               58  ;
              
               ID_NUMBER     VALUE1      TOTAL
              ---------- ---------- ----------
                       1          0
                       2          4
                       3          4
                       4         -2         -2
                       5          4
                       6          6
                       7         -3         -3
                       8         -2         -5
                       9         -2         -7
                      10          2
                      11          4
              
              11 rows selected.
              I'm unable to do it simpler :)
              Regards,
              Dima
              • 4. Re: Sum over group
                NicloeiW
                Hi Friend,


                Something like this,

                with t1 as
                  (select 1 as ID_NUMBER, 0 as VALUE1 from dual union all
                   select 2, 4 from dual union all
                   select 3, 4 from dual union all
                   select 4, -2 from dual union all
                   select 5, 4 from dual union all
                   select 6, 6 from dual union all
                   select 7, -3 from dual union all
                   select 8, -2 from dual union all
                   select 9, -2 from dual union all
                   select 10, 2 from dual union all
                   select 11, 4 from dual
                )
                Select id_number, value1, neg_sum,
                      Case When nvl(neg_sum,0) + abs(Max(neg_sum) over()) > 0 Then Null
                           When nvl(neg_sum,0) + abs(Max(neg_sum) over()) = 0  Then Max(neg_sum) over()
                      Else nvl(neg_sum,0) + abs(Max(neg_sum) over())
                      End total
                From
                (
                select id_number,
                       value1,
                       DECODE(SIGN(value1),
                               -1,
                                  SUM(DECODE(SIGN(value1),
                                              -1, value1, 0)
                                     ) OVER(partition by DECODE(SIGN(value1),-1, 1,0) ORDER BY id_number)
                                    ) neg_sum
                from t1
                order by 1
                ) ;

                ID_NUMBER     VALUE1    NEG_SUM      TOTAL
                ---------- ---------- ---------- ----------
                         1          0           
                         2          4           
                         3          4           
                         4         -2         -2         -2
                         5          4           
                         6          6           
                         7         -3         -5         -3
                         8         -2         -7         -5
                         9         -2         -9         -7
                        10          2           
                        11          4           

                11 rows selected

                @Dima: Used your with clause ;-) hope you dont mind it....
                Message was edited by:
                Nicloei W
                • 5. Re: Sum over group
                  Aketi Jyuuzou
                  This is a interesting question.
                  with t1 as (select 1 as ID_NUMBER, 0 as VALUE1 from dual union
                  select 2, 4 from dual union
                  select 3, 4 from dual union
                  select 4, -2 from dual union
                  select 5, 4 from dual union
                  select 6, 6 from dual union
                  select 7, -3 from dual union
                  select 8, -2 from dual union
                  select 9, -2 from dual union
                  select 10, 2 from dual union
                  select 11, 4 from dual union
                  select 12, 1 from dual union
                  select 13, 2 from dual union
                  select 14, -1 from dual union
                  select 15, -3 from dual union
                  select 16, -9 from dual union
                  select 17,  9 from dual union
                  select 18, -8 from dual)
                  select ID_NUMBER,VALUE1,
                  case when sign(VALUE1) = -1
                       then sum(Least(VALUE1,0)) over(order by ID_NUMBER) end as Sum1,
                  case when sign(VALUE1) = -1
                       then sum(VALUE1) over(partition by partID order by ID_NUMBER) end as Sum2
                  from (select ID_NUMBER,VALUE1,
                         ID_NUMBER - Row_Number() over(partition by sign(VALUE1)
                                     order by ID_NUMBER) as partID
                          from t1)
                  order by ID_NUMBER;
                  ID_NUMBER  VALUE1   SUM1  SUM2
                  ---------  ------  -----  ----
                          1       0   null  null
                          2       4   null  null
                          3       4   null  null
                          4      -2     -2    -2
                          5       4   null  null
                          6       6   null  null
                          7      -3     -5    -3
                          8      -2     -7    -5
                          9      -2     -9    -7
                         10       2   null  null
                         11       4   null  null
                         12       1   null  null
                         13       2   null  null
                         14      -1    -10    -1
                         15      -3    -13    -4
                         16      -9    -22   -13
                         17       9   null  null
                         18      -8    -30    -8
                  I used sense of "Tabibitozan".

                  Thread in which I mentioned "Tabibitozan" and deals alike queistions.
                  Re: Group by preserving the order
                  • 6. Re: Sum over group
                    Aketi Jyuuzou
                    create table t1 as
                    select 1 as ID_NUMBER, -1 as VALUE1 from dual union
                    select 2, -1 from dual union
                    select 3, 4 from dual union
                    select 4, 2 from dual union
                    select 5, -4 from dual union
                    select 6, -6 from dual union
                    select 7, -2 from dual union
                    select 8, -2 from dual union
                    select 10, 2 from dual union
                    select 9, 2 from dual union
                    select 11, 4 from dual union
                    select 12, 1 from dual union
                    select 13, 2 from dual union
                    select 14, -1 from dual union
                    select 15, -3 from dual union
                    select 16, -9 from dual union
                    select 17,  9 from dual union
                    select 18, -8 from dual;
                    select ID_NUMBER,VALUE1,
                    case when sign(VALUE1) = -1
                         then sum(Least(VALUE1,0)) over(order by ID_NUMBER) end as Sum1,
                    case when sign(VALUE1) = -1
                         then sum(VALUE1) over(partition by partID order by ID_NUMBER) end as Sum2
                    from (select ID_NUMBER,VALUE1,
                           ID_NUMBER - Row_Number() over(partition by sign(VALUE1)
                                       order by ID_NUMBER) as partID
                            from t1)
                    order by ID_NUMBER;
                    ID_NUMBER  VALUE1       SUM1       SUM2
                    ---------  ------  ---------  ---------
                            1      -1         -1         -1
                            2      -1         -2         -2
                            3       4       null       null
                            4       2       null       null
                            5      -4         -6          2
                            6      -6        -12         -4
                            7      -2        -14         -6
                            8      -2        -16         -8
                            9       2       null       null
                           10       2       null       null
                           11       4       null       null
                           12       1       null       null
                           13       2       null       null
                           14      -1        -17         -1
                           15      -3        -20         -4
                           16      -9        -29        -13
                           17       9       null       null
                           18      -8        -37         -8
                    Wow!
                    I had mistake when data is above data.

                    These below solutions are correct.
                    select ID_NUMBER,VALUE1,
                    case when sign(VALUE1) = -1
                         then sum(Least(VALUE1,0)) over(order by ID_NUMBER) end as Sum1,
                    case when sign(VALUE1) = -1
                         then sum(Least(VALUE1,0)) over(partition by partID order by ID_NUMBER) end as Sum2
                    from (select ID_NUMBER,VALUE1,
                           ID_NUMBER - Row_Number() over(partition by sign(VALUE1)
                                       order by ID_NUMBER) as partID
                            from t1)
                    order by ID_NUMBER;
                    or
                    select ID_NUMBER,VALUE1,
                    case when sign(VALUE1) = -1
                         then sum(Least(VALUE1,0)) over(order by ID_NUMBER) end as Sum1,
                    case when sign(VALUE1) = -1
                         then sum(VALUE1) over(partition by sign(VALUE1),partID order by ID_NUMBER) end as Sum2
                    from (select ID_NUMBER,VALUE1,
                           ID_NUMBER - Row_Number() over(partition by sign(VALUE1)
                                       order by ID_NUMBER) as partID
                            from t1)
                    order by ID_NUMBER;
                    ID_NUMBER  VALUE1  SUM1  SUM2
                    ---------  ------  ----  ----
                            1      -1    -1    -1
                            2      -1    -2    -2
                            3       4  null  null
                            4       2  null  null
                            5      -4    -6    -4
                            6      -6   -12   -10
                            7      -2   -14   -12
                            8      -2   -16   -14
                            9       2  null  null
                           10       2  null  null
                           11       4  null  null
                           12       1  null  null
                           13       2  null  null
                           14      -1   -17    -1
                           15      -3   -20    -4
                           16      -9   -29   -13
                           17       9  null  null
                           18      -8   -37    -8
                    • 7. Re: Sum over group
                      lmconsite
                      <p>JS1, Dima, Nicloei, Aketi,</p>
                      <p>Thanks for your help. I knew this was the place to come.</p>
                      <p>Aketi,</p>
                      <p>I made a minor change to your very last query to consider gaps and repetitions in
                      the original ID_NUMBER:</p>
                      <p><font face="Courier New">SELECT id_number, r_id_number, value1,<br>
                            
                      CASE<br>
                              
                      WHEN sign(value1) = -1<br>
                              
                      THEN SUM(least(value1, 0)) OVER(ORDER BY r_id_number)<br>
                            
                      END AS sum1,<br>
                            
                      CASE<br>
                              
                      WHEN sign(value1) = -1<br>
                              
                      THEN SUM(value1) OVER(PARTITION BY SIGN(value1), partid ORDER BY r_id_number)<br>
                            
                      END AS sum2<br>
                      FROM  (SELECT id_number, r_id_number, value1,<br>
                                   
                      r_id_number - ROW_NUMBER() OVER(PARTITION BY SIGN(value1) ORDER BY r_id_number)
                      AS partid<br>
                            
                      FROM  (SELECT id_number, ROW_NUMBER() OVER(ORDER BY id_number) AS r_id_number,<br>
                                          
                      value1<br>
                                   
                      FROM   t1))<br>
                      ORDER BY r_id_number;</font></p>
                      <p><font face="Arial">now returns:</font></p>
                      <p class="MsoNormal"><font face="Courier New">ID_NUMBER R_ID_NUMBER
                      VALUE1      SUM1      SUM2<br>
                      1                   1      0       
                              <br>
                      2                   2      4       
                             <br>
                      3                   3      4       
                              <br>
                      4                   4     -2       
                      -2        -2<br>
                      5                   5      4       
                              <br>
                      6                   6      6       
                              <br>
                      7                  
                      7     -3       
                      -5        -3<br>
                      8                   8     -2       
                      -7        -5<br>
                      9                   9     -2   
                          -9        -7<br>
                      10                 10      2       
                              <br>
                      11                 11      4       
                              <br>
                      12                 12      1       
                              <br>
                      13                 13      2       
                              <br>
                      14                 14     -1      
                      -10        -1<br>
                      15                 15     -3      
                      -13        -4<br>
                      16                 16     -9      
                      -22       -13<br>
                      17                 17      9       
                              <br>
                      18                 18     -8      
                      -30        -8<br>
                      20                 19     -5      
                      -35       -13<br>
                      21                 20      0       
                              <br>
                      25                 21      4       
                              <br>
                      30                 22      8       
                              <br>
                      31                 23     -4      
                      -39        -4<br>
                      32                 24     -4      
                      -43        -8<br>
                      33                 25      8       
                              <br>
                      33                 26     -8      
                      -51        -8<br>
                      33                 27    -10      
                      -61       -18<br>
                      34                 28     -5      
                      -66       -23</font></p>
                      <p class="MsoNormal"> </p>
                      <p class="MsoNormal">Thanks again. I got what I expected.</p>
                      <p class="MsoNormal"> </p>
                      <p class="MsoNormal">Luis</p>
                      <p> </p>