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

# Sum over group

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>
• ###### 1. Re: Sum over group
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
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
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
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
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
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
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>