query

Database
Enthusiast

query

Hi,

I have two tables one is having account eod balance. second tables is transaction.

Ex1:

table A

acc no=101 balance 500

table B

101 500
101 700
101 -900

But output should be:

101 1000
101 1700
101 800

Can you please help me?

4 REPLIES
Enthusiast

Re: query

Past two weeks I am trying this. Could you please help me.
Enthusiast

Re: query

Hi srivalli,

You need the acum by account ?, what is the order of rows ?, a date column?
Your correct output will be :
101 500 ( 0 + 500)
101 1200 ( 500 + 700)
101 300 (1200 + -900)

Try this:

SELECT account, sum(balance) OVER (partition by account order by account
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM table_A

Regards.
Senior Apprentice

Re: query

Hi srivalli,

select a.acc_no,
a.balance + b.cumulative_sum
from table_a as a
join
(select acc_no,
sum(amt) over (partition acc_no
order by datecol
rows unbounded preceding) as cumulative_sum
from table_b
) as a
on a.acc_no = b.acc_no

Dieter
Enthusiast

Re: query

thank your very much. great people