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 believe you just need to SUM the Balance Column and then GROUP BY Record,Type, In, Out    


    Kindest Regards,

  • 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. 

    RandyHelpdesk: 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&nbsp

    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

  • Maybe this would be better - no need for in or out to be zero in Record 1. 

    SET @Balance = Balance = CASE WHEN Record = 1 THEN Balance ELSE @Balance + In - Out END

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • 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. 

    RandyHelpdesk: 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