Calculation in two columns of different row in sql

Question

I have table t1

ID    NAME        AGE GENDER   BALANCE
----- ----- --------- ----- ---------
1001  John         10 M            10
1002  Meena         5 F             0
1003  Nikh         11 M             0
1004  divs          7 F             0
1005  neha          4 F             0

from second row, if Gender is M then Balance (2nd row) should be age(2)+balance(1)

else Balance(1)-age(2)

Final structure should be like

ID    NAME        AGE GENDER   BALANCE
----- ----- --------- ----- ---------
1001  John         10 M            10
1002  Meena         5 F             5
1003  Nikh         11 M             16
1004  divs          7 F             9
1005  neha          4 F             5

Please help me with query /procedure

Answer 1

How about something like this?

with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
                     select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
                     select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
                     select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
                     select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual)
select id,
       name,
       age,
       gender,
       sum(case when gender = 'F' then -1 * age else age end) over (order by id) balance
from   sample_data;

        ID NAME         AGE GENDER    BALANCE
---------- ----- ---------- ------ ----------
      1001 John          10 M              10
      1002 Meena          5 F               5
      1003 Nikh          11 M              16
      1004 divs           7 F               9
      1005 neha           4 F               5

I'm guessing that the balance of the first row (I'm assuming an order of id, here) is 10 because that's John's age and he's male, rather than it being some arbitrary number.

---

ETA: here are the alternatives to the above solution. I HIGHLY recommend you test everything against a production-like volume of data (where I've used the with clause to mimic a table called sample_data with 5 rows, you would just need to use your table). That way, you can get timings that should highlight the most performant method for your scenario; hopefully your manager won't be blind to facts (if he is, run. Run fast!)

1) SQL statement with no analytic functions:

with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
                     select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
                     select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
                     select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
                     select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual)
select sd1.id,
       sd1.name,
       sd1.age,
       sd1.gender,
       sum(case when sd2.gender = 'F' then -1 * sd2.age else sd2.age end) balance
from   sample_data sd1
       inner join sample_data sd2 on (sd1.id >= sd2.id)
group by sd1.id,
         sd1.name,
         sd1.age,
         sd1.gender
order by id;

        ID NAME         AGE GENDER    BALANCE
---------- ----- ---------- ------ ----------
      1001 John          10 M              10
      1002 Meena          5 F               5
      1003 Nikh          11 M              16
      1004 divs           7 F               9
      1005 neha           4 F               5

2) Procedural (slow-by-slow row-by-row {yawn}) method (NOT recommended):

create or replace procedure calc_balance1
as
  v_balance number := 0;
  cursor cur is
    with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
                     select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
                     select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
                     select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
                     select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual)
    select id,
           name,
           age,
           gender,
           balance
    from   sample_data;
begin
  for rec in cur
  loop
    v_balance := v_balance + case when rec.gender = 'F' then -1 * rec.age 
                                  else rec.age
                             end;
    dbms_output.put_line('id = '||rec.id||', name = '||rec.name||', age = '||rec.age||', gender = '||rec.gender||', balance = '||v_balance);
  end loop;
end calc_balance1;
/

begin
  calc_balance;
end;
/

id = 1001, name = John, age = 10, gender = M, balance = 10
id = 1002, name = Meena, age = 5, gender = F, balance = 5
id = 1003, name = Nikh, age = 11, gender = M, balance = 16
id = 1004, name = divs, age = 7, gender = F, balance = 9
id = 1005, name = neha, age = 4, gender = F, balance = 5

---

However, if you had to come up with a procedure for this, I'd use the query with an analytic function and just stick it in a ref cursor, eg:

create or replace procedure calc_balance2 (p_refcur out sys_refcursor)
as
begin
  open p_refcur for with sample_data as (select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
                                         select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
                                         select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
                                         select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
                                         select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual)
                    select id,
                           name,
                           age,
                           gender,
                           sum(case when gender = 'F' then -1 * age else age end) over (order by id) balance
                    from   sample_data
                    order by id;
end calc_balance2;
/

------------------

I see the procedure you wrote; here's how I would do it instead:

-- mimicking your test_divs table:
create table test_divs as
select 1001 id, 'John' name, 10 age, 'M' gender, 10 balance from dual union all
select 1002 id, 'Meena' name, 5 age, 'F' gender, 0 balance from dual union all
select 1003 id, 'Nikh' name, 11 age, 'M' gender, 0 balance from dual union all
select 1004 id, 'divs' name, 7 age, 'F' gender, 0 balance from dual union all
select 1005 id, 'neha' name, 4 age, 'F' gender, 0 balance from dual;

create or replace procedure t1_d12_v2
as
begin
  merge into test_divs tgt
  using (select id,
                name,
                age,
                gender,
                sum(case when gender = 'F' then -1 * age else age end) over (order by id) balance
         from   test_divs) src
    on (tgt.id = src.id)
  when matched then
    update set tgt.balance = src.balance;
end t1_d12_v2;
/

select * from test_divs;

        ID NAME         AGE GENDER    BALANCE
---------- ----- ---------- ------ ----------
      1001 John          10 M              10
      1002 Meena          5 F               0
      1003 Nikh          11 M               0
      1004 divs           7 F               0
      1005 neha           4 F               0

begin
  t1_d12_v2;
  commit;
end;
/

select * from test_divs;

        ID NAME         AGE GENDER    BALANCE
---------- ----- ---------- ------ ----------
      1001 John          10 M              10
      1002 Meena          5 F               5
      1003 Nikh          11 M              16
      1004 divs           7 F               9
      1005 neha           4 F               5

I HIGHLY suggest you test both methods with your production-like data and see which one performs better. (If your manager is really dead set against the analytic function, I would swap the "src" subquery to be the other sql statement that I came up with - the one with the join and group by.)

Doing updates row-by-row like you're doing is going to be doing context switches between sql and pl/sql twice for every pass through the loop. Why bother, when you could do the whole thing in a single sql statement. Seriously.

Answer 2

   create or replace procedure t1_d12
 as
 b  int :=0;
 p test_divs%rowtype;
 cursor test is select * from test_divs order by id;

 begin 
 for p in test
 loop
 if p.gender = 'M' then
 b :=p.age+b;
 elsif p.gender = 'F' then
 b :=b-p.age;
 end if;
 dbms_output.put_line(b);
 update test_divs set balance = b where id=p.id;
 end loop;

Answer 3

select ID, NAME, AGE, GENDER,  @b:=if(GENDER='M', @b+AGE, @b-AGE) BALANCE 
  from t1, (select @b:=0) b

Demo on sqlfiddle.com