This discussion is archived
6 Replies Latest reply: Feb 8, 2013 7:11 AM by Solomon Yakobson RSS

SQL: How to cumulatively sum up values till a condition is satisfied?

337552 Newbie
Currently Being Moderated
Hello all,

I need to write a SQL to fulfill a particular requirement.
For each row, the values in Field2 should be cumulatively summed up to see when it will be equal to the value of Field1
Eg. In the following example, Field1 has 15. Field2 values will be summed up cumulatively till they reach 15 (03-JAN-2013)
For the next record Field1 is 8, so Field2 can match 8 on 05-JAN-13.

Day Field1 Field2
01-JAN-2013 15 10          
02-JAN-2013 8 4
03-JAN-2013 9 1
04-JAN-2013 12 2
05-JAN-2013 7 2

Can this be achieved through SQL?
Using Analytical Functions?

Please help.

Regards,
Sam

Edited by: sammydude on Feb 7, 2013 5:24 AM
  • 1. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
    AlbertoFaenza Expert
    Currently Being Moderated
    sammydude wrote:
    Hello all,

    I need to write a SQL to fulfill a particular requirement.
    For each row, the values in Field2 should be cumulatively summed up to see when it will be equal to the value of Field1
    Eg. In the following example, Field1 has 15. Field2 values will be summed up cumulatively till they reach 15 (03-JAN-2013)
    For the next record Field1 is 8, so Field2 can match 8 on 05-JAN-13.

    Day Field1 Field2
    01-JAN-2013 15 10          
    02-JAN-2013 8 4
    03-JAN-2013 9 1
    04-JAN-2013 12 2
    05-JAN-2013 7 2

    Can this be achieved through SQL?
    Using Analytical Functions?

    Please help.

    Regards,
    Sam

    Edited by: sammydude on Feb 7, 2013 5:24 AM
    What is the expected output?
    What is the sum of field2 does not match precisely field1 (i.e is greater or less)?

    Regards.
    Al
  • 2. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
    337552 Newbie
    Currently Being Moderated
    I would probably need the difference in days between the current row's date and the date on which Field1 value will be met.
    If cumulative Field2 value is greater than Field1, then we stop searching.
    If cumulative Field2 value is greater than Field1, then we continue to the following day.

    Thanks,
    Sam
  • 3. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    Try this:
    with tab as
    (
    select to_date('01-JAN-2013','dd-mon-yyyy') Day, 15 Field1, 10 Field2 from dual union all
    select to_date('02-JAN-2013','dd-mon-yyyy') Day, 8  Field1, 4  Field2 from dual union all
    select to_date('03-JAN-2013','dd-mon-yyyy') Day, 9  Field1, 1  Field2 from dual union all
    select to_date('04-JAN-2013','dd-mon-yyyy') Day, 12 Field1, 2  Field2 from dual union all
    select to_date('05-JAN-2013','dd-mon-yyyy') Day, 7  Field1, 2  Field2 from dual
    /*DAY       FIELD1 FIELD2
    --------- ------ ------
    01-JAN-13     15     10 
    02-JAN-13      8      4 
    03-JAN-13      9      1 
    04-JAN-13     12      2 
    05-JAN-13      7      2 
    */
    )
    ,tab_sum as
    (
    select
      t1.day    t1_day
      ,t2.day   t2_day
      ,t1.field1
      ,t2.field2
      ,sum(t2.field2) over (partition by t1.day order by t2.day rows between unbounded preceding and current row) sf2
    
    from
      tab t1 join
      tab t2 on (t1.day <= t2.day)
    /*
    T1_DAY    T2_DAY    FIELD1 FIELD2 SF2
    --------- --------- ------ ------ ---
    01-JAN-13 01-JAN-13     15     10  10 
    01-JAN-13 02-JAN-13     15      4  14 
    01-JAN-13 03-JAN-13     15      1  15 
    01-JAN-13 04-JAN-13     15      2  17 
    01-JAN-13 05-JAN-13     15      2  19 
    02-JAN-13 02-JAN-13      8      4   4 
    02-JAN-13 03-JAN-13      8      1   5 
    02-JAN-13 04-JAN-13      8      2   7 
    02-JAN-13 05-JAN-13      8      2   9 
    03-JAN-13 03-JAN-13      9      1   1 
    03-JAN-13 04-JAN-13      9      2   3 
    03-JAN-13 05-JAN-13      9      2   5 
    04-JAN-13 04-JAN-13     12      2   2 
    04-JAN-13 05-JAN-13     12      2   4 
    05-JAN-13 05-JAN-13      7      2   2 
    */  
    )
    
    select
      t1_day
      ,max(t2_day)  t2_day
      ,field1
      ,max(sf2)     sum_field2
      
    from
      tab_sum
    
    where
      sf2 <= field1
    
    group by
      t1_day
      ,field1
    
    order by
      t1_day
    ;
    
    T1_DAY    T2_DAY    FIELD1 SUM_FIELD2
    --------- --------- ------ ----------
    01-JAN-13 03-JAN-13     15         15 
    02-JAN-13 04-JAN-13      8          7 
    03-JAN-13 05-JAN-13      9          5 
    04-JAN-13 05-JAN-13     12          4 
    05-JAN-13 05-JAN-13      7          2 
    Note for the day = 02-jan-2013 you can not reach 8 unles you skip day 03-jan-2013.

    Regards,

    Peter
  • 4. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
    chris227 Guru
    Currently Being Moderated
    with data as (
    select date '2013-01-01' d, 15 x,10 y from dual union all
    select date '2013-01-02', 8 , 4 from dual union all
    select date '2013-01-03', 9,1 from dual union all
    select date '2013-01-04', 12,2 from dual union all
    select date '2013-01-05', 7,2 from dual)
    , sums as (
    select
    d,x,y,
    sum(y) over (order by d) s
    from data)
     
    select
     s1.d
    ,s1.x
    ,s1.y
    ,min(s2.d) date_reached
    ,max(s2.s) keep (dense_rank first order by s2.d)
     -s1.s + s1.y
     sum_reached
    from
     sums s1
    ,sums s2
    where
    s1.x-s1.y <= s2.s-s1.s
    group by
     s1.d
    ,s1.x
    ,s1.y
    ,s1.s
    order by d
    
    D     X     Y     DATE_REACHED     SUM_REACHED
    01.01.2013     15     10     03.01.2013     15
    02.01.2013     8     4     05.01.2013     9
    For alternative please give your 4-digit database version, because there is a drawback with this kind of join-based solutions:
    Many rows are joined but in fact we need only one (the one with the lowest date).
    So i would do it with recursive subquery on 11g and consider model in 10g.

    Edited by: chris227 on 07.02.2013 05:59

    Edited by: chris227 on 07.02.2013 06:27
    added sum_reached

    Edited by: chris227 on 07.02.2013 06:36
    corrected sum_reached

    Edited by: chris227 on 07.02.2013 08:34
  • 5. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
    Solomon Yakobson Guru
    Currently Being Moderated
    sammydude wrote:
    Eg. In the following example, Field1 has 15. Field2 values will be summed up cumulatively till they reach 15 (03-JAN-2013)
    For the next record Field1 is 8, so Field2 can match 8 on 05-JAN-13.
    Is this what you need:
    with t1 as (
                select  day,
                        field2,
                        connect_by_root day root_day,
                        connect_by_root field1 root_field1
                  from  tbl
                  connect by day = prior day + 1
               ),
         t2 as (
                select  day,
                        root_day,
                        root_field1,
                        field2,
                        sum(field2) over(partition by root_day order by day) - field2 sum_prev_field2
                  from  t1
               )
    select  root_day from_day,
            max(day) to_day
      from  t2
      where root_field1 > sum_prev_field2
        and root_field1 <= sum_prev_field2 + field2
      group by root_day
      order by from_day
    /
    
    FROM_DAY  TO_DAY
    --------- ---------
    01-JAN-13 03-JAN-13
    02-JAN-13 05-JAN-13
    
    SQL> 
    SY.
  • 6. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
    Solomon Yakobson Guru
    Currently Being Moderated
    Solomon Yakobson wrote:Is this what you need:
    Or perhaps:
    with t1 as (
                select  day,
                        field2,
                        connect_by_root day root_day,
                        connect_by_root field1 root_field1,
                        connect_by_isleaf leaf
                  from  tbl
                  connect by day = prior day + 1
               ),
         t2 as (
                select  day,
                        root_day,
                        root_field1,
                        field2,
                        leaf,
                        sum(field2) over(partition by root_day order by day) - field2 sum_prev_field2
                  from  t1
               )
    select  root_day from_day,
            max(day) to_day
      from  t2
      where root_field1 > sum_prev_field2
        and (
                root_field1 <= sum_prev_field2 + field2
             or
                leaf = 1
            )
      group by root_day
      order by from_day
    /
    
    FROM_DAY  TO_DAY
    --------- ---------
    01-JAN-13 03-JAN-13
    02-JAN-13 05-JAN-13
    03-JAN-13 05-JAN-13
    04-JAN-13 05-JAN-13
    05-JAN-13 05-JAN-13
    
    SQL> 
    SY.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points