Forum Stats

  • 3,757,579 Users
  • 2,251,249 Discussions
  • 7,869,869 Comments

Discussions

Sum over group

lmconsite
lmconsite Member Posts: 29
edited Nov 6, 2007 12:17PM in SQL & PL/SQL
<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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4<br>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2<br>5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4<br>6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6<br>7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -3<br>8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2<br>9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2<br>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2<br>
11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-2<br>5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-5<br>8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-7<br>9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-9<br>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>
11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-2<br>5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-3<br>8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-5<br>9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-7<br>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>
11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</font><br><br>Is there a way to accomplish this using analytic functions only?<br>
<br>Thanks in advance,<br><br>Luis</p>

Comments

  • 584412
    584412 Member Posts: 1,329
    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
    /
  • lmconsite
    lmconsite Member Posts: 29
    <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>
    &nbsp;</font></p>
    <p><font face="Courier New">ID_NUMBER VALUE1 JS1_SUM SUPPOSED_SUM<br>
    1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
    2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    4 <br>
    3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    4 <br>
    4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2<br>
    5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    4 <br>
    6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    6 <br>
    7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -3<br>
    8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -5<br>
    9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -7<br>
    10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2 <br>
    11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4
    </font></p>
    <p><font face="Arial">Thanks,</font></p>
    <p><font face="Arial">Luis</font></p>
  • 170207
    170207 Member Posts: 440
    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
  • NicloeiW
    NicloeiW Member Posts: 1,811
    edited Nov 5, 2007 8:18PM
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 6, 2007 6:30AM
    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.
    2127274
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
  • lmconsite
    lmconsite Member Posts: 29
    <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>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    CASE<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    WHEN sign(value1) = -1<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    THEN SUM(least(value1, 0)) OVER(ORDER BY r_id_number)<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    END AS sum1,<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    CASE<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    WHEN sign(value1) = -1<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    THEN SUM(value1) OVER(PARTITION BY SIGN(value1), partid ORDER BY r_id_number)<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    END AS sum2<br>
    FROM&nbsp; (SELECT id_number, r_id_number, value1,<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    r_id_number - ROW_NUMBER() OVER(PARTITION BY SIGN(value1) ORDER BY r_id_number)
    AS partid<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    FROM&nbsp; (SELECT id_number, ROW_NUMBER() OVER(ORDER BY id_number) AS r_id_number,<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    value1<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    FROM&nbsp;&nbsp; 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&nbsp;R_ID_NUMBER
    VALUE1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM2<br>
    1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -2<br>
    5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    7&nbsp;&nbsp;&nbsp;&nbsp; -3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -3<br>
    8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -5<br>
    9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp; -2&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;-9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -7<br>
    10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 14&nbsp;&nbsp;&nbsp;&nbsp; -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -1<br>
    15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 15&nbsp;&nbsp;&nbsp;&nbsp; -3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -4<br>
    16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 16&nbsp;&nbsp;&nbsp;&nbsp; -9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -13<br>
    17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 18&nbsp;&nbsp;&nbsp;&nbsp; -8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -8<br>
    20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19&nbsp;&nbsp;&nbsp;&nbsp; -5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -35&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -13<br>
    21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    31&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; 23&nbsp;&nbsp;&nbsp;&nbsp; -4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -39&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -4<br>
    32&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24&nbsp;&nbsp;&nbsp;&nbsp; -4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -43&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -8<br>
    33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>
    33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26&nbsp;&nbsp;&nbsp;&nbsp; -8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -51&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -8<br>
    33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; 27&nbsp;&nbsp;&nbsp;&nbsp;-10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -61&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -18<br>
    34&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 28&nbsp;&nbsp;&nbsp;&nbsp; -5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    -66&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -23</font></p>
    <p class="MsoNormal">&nbsp;</p>
    <p class="MsoNormal">Thanks again. I got what I expected.</p>
    <p class="MsoNormal">&nbsp;</p>
    <p class="MsoNormal">Luis</p>
    <p>&nbsp;</p>
This discussion has been closed.