How Can I auto sum a Column for every record I select?
-
How Can I auto sum a Column for every record I select?
for Example :
In this example every record is from 3 different table, I retrieve it using UNION operator.
Result so far
Record Type In Out Balance
1 Balance 0 0 1000
2 aa 100 0 0
3 bb 0 50 0
Result I want
Record Type In Out Balance
1 Balance 0 0 1000
2 aa 100 0 1100
3 bb 0 50 1050
Please Help Me. Thanks.
-
I've made a couple of assumptions... calling your table or view "TRX" and assuming that the [record] field is unique. You can try something along the lines of:
select [Record],[Type],[In],[Out], [Balance] = -- -- Grab the balance field from the most record "balance" row. -- (select [balance] from Trx where Trx.[Record] = [c].[LastBalanceRecord]) + -- -- Add all the INs and subtract all the OUTs since, including that of the current row -- (select [balance] = isnull(sum([In]),0)-isnull(sum([Out]),0) from Trx where Trx.[Record] > [c].[LastBalanceRecord] and Trx.[Record] <=[c].[Record]) from ( -- -- This bit ([c]) returns all the columns of the TRX row, as well as the record number of -- the most recent "balance" row, from where the running balance starts. -- select *, [LastBalanceRecord] = (select max([Record]) from Trx b where b.[Record] <= a.[Record] and [Type]='Balance') from trx a ) as [c] order by 1
Cheers,
- Mark -
Oracle supports a SUM OVER function that allows a rolling sum for a row. Its quite flexible in that it can be used
to sum over all rows, or a subset of rows based on column breaks etc.
If you are accessing sql server via MS access, its supports a DSUM function which performs the same task.
I've generally found queries such as the one above to be fairly slow because for each row in your
output result, you are independently computing the sums of all the rows you've retrieved prior to that row.
So if the output result contains 10 rows, the first row will be retrieved 9 times to compute the calculated sum
for rows 2 - 10, the second row 8 times etc. This can tend to get very inefficient.
I've never found the ACCESS dsum function to be overly efficient either.
-
Since you're processing the records one at a time forward, perhaps an update query using local variables will work. Putting an order by into an update is not easy - I used a join. You might be able to use a clustered index instead of the "order by" via a join. Perhaps there is a better way - I'd be glad to see it. This also assumes that the first record is the only "balance" record with in and out zero. All other records are assumed to have a zero balance value before the update.
DECLARE @Balance as money
SET @Balance = 0.
UPDATE BalanceTable
SET @Balance = Balance = @Balance + Balance + In - Out
FROM BalanceTable b
INNER JOIN (
SELECT TOP 100 PERCENT Record
FROM BalanceTable
ORDER BY Record
) r ON
r.Record= b.Record
Here is a possible alternate for the set to use the Balance only in the first record. The query could be run many times without setting the balances to zero. A CASE statement tests the Record value uses the running balance in all but Record 1.
SET @Balance = Balance = In - Out + (CASE WHEN Record = 1 THEN Balance ELSE @Balance END)
This might work. Let me know if it does - I have not tried it.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
You Could also use a TRIGGER TO MAINTAIN Such Calculations in a denormalized way at Insert / Update time (it is not an expensive opperation 
And you will select your balance in one speedy read only query
But if you really need to do it real time here you go:
select Record, Type, [In], [Out],
(select Sum( BList)
from (
select Record
, (case when Type = 'balance' then balance
when [in] <> 0 Then [in]
when [out] <> 0 Then -[Out]
else 0 End) As BList
from Balances ) B2
where B2.Record <= B1.record) As Balance
from Balances B1
* Noel -
The self-join was made to order for such queries, provided that the number of rows were relatively low.
example:
select x.record,x.type,x.[in],x.out,x.balance,cum_total=sum(y.balance+y.[in]+y.out)
from co_test x inner join co_test y
on x.record>=y.record
group by x.record,x.type,x.[in],x.out,x.balance
result:
record type in out balance cum_total
----------- ---------- ------------ ------------ ------------ ----------------------------------------
1 Balance .00 .00 1000.00 1000.00
2 aa 100.00 .00 .00 1100.00
3 bb .00 50.00 .00 1150.00
Charles
-
The update query with local variables could also work if the sums are account specific. A local variable could hold the account number. If the account number changes, the @Balance is not used.
SELECT @AccountId = null, @Balance = 0.
UPDATE BalanceTable
SET @Balance = Balance = CASE WHEN AccountId = @AccountId THEN @Balance + In - Out ELSE Balance END,
@AccountId = AccountId
FROM BalanceTable b
INNER JOIN (
SELECT TOP 100 PERCENT AccountId, Record
FROM BalanceTable
ORDER BY AccountId, Record
) r ON
r.AccountId = b.AccountId and r.Record= b.Record
It seems like this would be faster than a self join with x.record>=y.record.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉 -
the problem with an Update like that is that it will LOCK THE WHOLE TABLE !!
It will probably more efficient to use a cursor with ordered results and in that way you can run only once through the whole data set
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply