SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist perpetual_dream's Avatar
    Join Date
    Nov 2005
    Location
    Jerusalem
    Posts
    542
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    display balance as a column on each row

    Hello,

    I have a table (Transaction) which has the field amount (which can be either negative or positive), each transaction has a date and transaction_id.

    I would like to display all the transactions with the balance (up to the transaction date) as a separate column calculated on the fly (there is no balance column in the table).

    Ex:

    Transaction_id------Date----Amount----Balance_up_to_transaction

    1 ----------------28-02-2009 -- 55 ------ 55

    2 ----------------8-03-2009 ---- -33 ------ 22

    3----------------9-04-2009 ----- 15 ------- 27

    How can this be done using SQL ? The problem is that the balance is Dependant on the sum of all the amounts prior to the transaction ?

    Many Thanks.
    Tanzeelnet - Best arabic downloadsite
    Linux Hosting |Free Downloads |

  2. #2
    SitePoint Evangelist perpetual_dream's Avatar
    Join Date
    Nov 2005
    Location
    Jerusalem
    Posts
    542
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anyone?
    Tanzeelnet - Best arabic downloadsite
    Linux Hosting |Free Downloads |

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why does it have to be done with sql?

    this is a good example of a situation where you should probably do the running balance in your application language as you print the rows retrieved from the database
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist perpetual_dream's Avatar
    Join Date
    Nov 2005
    Location
    Jerusalem
    Posts
    542
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    because my display includes paging and I will lose the current balance on the next page
    Tanzeelnet - Best arabic downloadsite
    Linux Hosting |Free Downloads |

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that depends on your paging algorithm, doesn't it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist perpetual_dream's Avatar
    Join Date
    Nov 2005
    Location
    Jerusalem
    Posts
    542
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What if I jump from the 1st page to the third page ? I will lose the balance which is supposed to be calculated according to the sum in the previous rows .

    A second questions: I didn't save the balance in the database, Because I heard that it is not generally a good practice to save "calculated field" , is it true?
    Tanzeelnet - Best arabic downloadsite
    Linux Hosting |Free Downloads |

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes it's true

    here you go, the sql solution --
    Code:
    SELECT this.Transaction_id
         , this.Date
         , this.Amount
         , ( SELECT SUM(Amount)
               FROM daTable
              WHERE Date < this.Date
                 OR Date = this.Date
                AND Transaction_id < this.Transaction_id
           ) AS Balance_up_to_transaction
      FROM daTable AS this
    ORDER
        BY this.Date
         , this.Transaction_id
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Evangelist perpetual_dream's Avatar
    Join Date
    Nov 2005
    Location
    Jerusalem
    Posts
    542
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you
    Tanzeelnet - Best arabic downloadsite
    Linux Hosting |Free Downloads |


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •