Hi,

I'm trying to perform a query that will give me a running sum of a list of
records. (Think about a bank account, with the sums added and subtracted,
with me wanting to create a balance field at the end).

I've found I can do this via Runsum.

However, in my query I would like to have multiple bank accounts showing,
with the RunSum resetting whenever the account field changes.

ie, something like:

Select Account, DateTime, Amount, Runsum(Amount) as Balance
From Transactions
Group by Account,
Order by Account, Datetime.

..... but this doesn't seem to be working with what I've tried.

Is it possible to reset the runsum counter, or is this not possible to do
with SQL, forcing me to perform a seperate query per account?

Thanks & Regards

Adam.

Adam,

<< .... but this doesn't seem to be working with what I've tried. >>

Get rid of the DateTime column in the ORDER BY.  It doesn't make any sense
being there and is most likely screwing up the RUNSUM() calculation.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Get rid of the DateTime column in the ORDER BY. It doesn't make any sense
>being there and is most likely screwing up the RUNSUM() calculation.

From Adam's post

<<Think about a bank account, with the sums added and subtracted,
with me wanting to create a balance field at the end>>

DateTime does make sense for a bank statement.

Roy Lambert

Roy,

<< DateTime does make sense for a bank statement. >>

Yes, but not when grouping by account and wanting a balance for the account.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Adam H." <ahairsub4@pleaseREMOVEme.jvxp.com> wrote in message
news:8C2FABE3-B9ED-45A4-AFD0-C2D978A15A55@news.elevatesoft.com...
> Hi,
>
> I'm trying to perform a query that will give me a running sum of a list of
> records. (Think about a bank account, with the sums added and subtracted,
> with me wanting to create a balance field at the end).
>
> I've found I can do this via Runsum.
>

Hmmm, I don't think so. runsum will sum the groups. You want (if I
understand correctly) a running total on individual records, restarting as
you get a break in account number. This SQL adapted from a script I have
might do the trick for you.

select bankID, trdate, tramount, cast(0 as decimal) rd
into memory\temp
from rsum
order by bankid, trdate;
optimize table memory\temp nobackup;
alter table memory\temp add a autoinc;
update memory\temp set rd = tramount where a = 1;
update memory\temp t1 set rd = if((t2.bankid = t1.bankid) or (t2.bankid =
null) then t2.rd + t1.tramount else t1.tramount)
from memory\temp t1
join memory\temp t2 on t2.a + 1 = t1.a;
select * from memory\temp;

If what you want is a total by day instead of individual records, change the
first select to group by date, bankID, the rest should work the same. If the
tables are large, add an index on field a before the updates.

Robert

Thanks Roy Tim and Robert for your replies!

Robert, this looks exactily like what I might need. I'll try adapting it and
see how it goes.

Best Regards

Adam.

> select bankID, trdate, tramount, cast(0 as decimal) rd
> into memory\temp
> from rsum
> order by bankid, trdate;
> optimize table memory\temp nobackup;
> alter table memory\temp add a autoinc;
> update memory\temp set rd = tramount where a = 1;
> update memory\temp t1 set rd = if((t2.bankid = t1.bankid) or (t2.bankid =
> null) then t2.rd + t1.tramount else t1.tramount)
> from memory\temp t1
> join memory\temp t2 on t2.a + 1 = t1.a;
> select * from memory\temp;