CODEWITHSUNDEEP
sqloracle-database
Help123
Help123

Reputation: 1443

Oracle SQL running total on change of field (SUM on column only when field changes)

I have a question in regards to how to SUM on a column only when a field is changing.

Take for example the table below:

Note that Column A and Column B are different tables. I.e. A was selected from Table X and B was selected from Table Y

SELECT X.A, Y.B
FROM X
INNER JOIN Y ON X.DATE = Y.DATE AND X.VAL1 = Y.VAL1 AND X.VAL2 = Y.VAL2

 A     B
123    5
123    5
456    10
789    15
789    15

I need to sum column B on change of field on column A:

I.e. the query should return 5 + 10 + 15 = 30 (5 the first time because value in column A is 123, 10 the second time because column A changed from 123 to 456 - note that the second row was skipped because column A still contains value 123 - hence the change of field logic and so on).

I can't do a simple SUM(B) because that would return 50. I also cannot do SUM(B) OVER (PARTITION BY A) because that would do a running total by group, not by change of field.

My output needs to look like this:

A    B    X
123  5    5
123  5    5
456  10   15
789  15   30
789  15   30

I am trying to do this within a simple query. Is there a particular function I can use to do this?

Upvotes: 1

Views: 739

Answers (3)

eifla001
eifla001

Reputation: 1157

you can also create a function and use it, see sample below,

create package test_pkg123
as
  a number;
  r_sum NUMBER;
  function get_r_sum(p_a number, p_val NUMBER, rown NUMBER) return number;
end;
/

create or replace package body test_pkg123
as
function get_r_sum(p_a number, p_val NUMBER, rown NUMBER) return number
  is
  begin
      if rown = 1 then
          r_sum := p_val;
          return r_sum;
      end if;
      if p_a != a then
         r_sum := nvl(r_sum, 0) + nvl(p_val, 0);
      end if;
      a := p_a;
      return r_sum;
  end;
end;
/

with test (a, b) as
    (select 123, 5 from dual union all
     select 123, 5 from dual union all
     select 456, 10 from dual union all
     select 789, 15 from dual union all
     select 789, 15 from dual union all
     select 789, 15 from dual union all
     select 123, 2 from dual
    )
select a, b, test_pkg123.get_r_sum(a, b, rownum) r_sum 
  from test;

Output:

     A          B      R_SUM
   123          5          5
   123          5          5
   456         10         15
   789         15         30
   789         15         30
   789         15         30
   123          2         32

7 rows selected

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142788

Ta-daaa?

SQL> with test (a, b) as
  2  (select 123, 5 from dual union all
  3   select 123, 5 from dual union all
  4   select 456, 10 from dual union all
  5   select 789, 15 from dual union all
  6   select 789, 15 from dual
  7  ),
  8  proba as(
  9  select a, b,
 10    case when a <> nvl(lag(a) over (order by a), 0) then 'Y' else 'N' end switch
 11  from test
 12  )
 13  select a, b,
 14    sum(decode(switch, 'Y', b, 0)) over (partition by null order by a) x
 15  from proba
 16  order by a;

         A          B          X
---------- ---------- ----------
       123          5          5
       123          5          5
       456         10         15
       789         15         30
       789         15         30

SQL>

Upvotes: 0

Chris Hep
Chris Hep

Reputation: 1141

For the simple data set provided, the following should work. You will, of course, want to review the ORDER BY clauses for correctness in your exact use case.

SELECT a
      ,b
      ,SUM(CASE WHEN a = prev_a THEN 0 ELSE b END) OVER (ORDER BY a RANGE UNBOUNDED PRECEDING) AS x
  FROM (
SELECT a
      ,b
      ,LAG(a) OVER (ORDER BY a) AS prev_a
  FROM {your_query}
  )

This solution makes use of the LAG function, which returns the specified column from the previous result. Then the outer query's SUM gives the value only when the previous row didn't have the same value. And there is also the windowing clause involved in the SUM because you specified that you needed a running total.

Upvotes: 0

Related Questions