This discussion is archived
7 Replies Latest reply: Nov 6, 2007 9:17 AM by lmconsite RSS

Sum over group

lmconsite Newbie
Currently Being Moderated
<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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    <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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    <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>