You can do this using a variable. Set the variable starting value with a "query": set @runningTotal := 0
Then add the calculation to your total: SELECT a. trans_id ,a.tran_date,b.cat_type,a.payee,a.amnt, @runningTotal := @runningTotal+a.amnt AS rollingTotal from transactions a join categories b on a.cat_id = b.cat_id where a.user_id=1 and a.acc_id=3 order by a.tran_date ASC Brent Baisley On Thu, Sep 3, 2009 at 1:56 PM, John Daisley<john.dais...@butterflysystems.co.uk> wrote: > Hi, > > Hoping someone can help me with this little issue! It seems really > simple but my brain is refusing to work. > > We have a transactions tables like so... > > mysql> desc transactions; > +-----------+----------------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra > | > +-----------+----------------------+------+-----+---------+----------------+ > | trans_id | int(10) unsigned | NO | PRI | NULL | > auto_increment | > | user_id | smallint(5) unsigned | NO | MUL | NULL | > | > | acc_id | smallint(5) unsigned | NO | MUL | NULL | > | > | tran_date | date | NO | | NULL | > | > | payee | varchar(25) | NO | | NULL | > | > | amnt | decimal(8,2) | NO | | NULL | > | > | cat_id | int(10) unsigned | NO | MUL | NULL | > | > +-----------+----------------------+------+-----+---------+----------------+ > 7 rows in set (0.00 sec) > > > > ...this joins to a few other tables and has the following simple query > run on it to show all transactions for a particular user and account. > > mysql> SELECT a. trans_id ,a.tran_date,b.cat_type,a.payee,a.amnt from > transactions a join categories b on a.cat_id = b.cat_id where > a.user_id=1 and a.acc_id=3 order by a.tran_date ASC; > > Which returns a list something like this... > > +-----------+-----------------+--------------+---------------------------+----------+ > trans_id | tran_date | cat_type | payee | amnt | > +-----------+-----------------+-------------+----------------------------+----------+ > | 1| 2009-08-31 | Income | Opening Balance | 0.00 | > | 3| 2009-09-02 | Income | Test Transactions | 0.20 | > | 23| 2009-09-02 | Income | Tester | 1.20 > | > | 102| 2009-09-02 | Income | Debit | -1.09 > | > +-----------+-----------------+-------------+----------------------------+----------+ > 4 rows in set (0.00 sec) > > Now this has been fine for a long time until this afternoon when I get a > call saying the query needs editing to add another column to the output > showing a rolling account balance. This means the current output show > above needs to change to something like this... > > +---------+---------------+------------+----------------------+-------+-------------------------+ > |trans_id | |tran_date | cat_type | payee | amnt | > Rolling Balance | > +---------+---------------+------------+----------------------+-------+-------------------------+ > | 1| 2009-08-31 | Income | Opening Balance | 0.00 | > 0.00 | > | 3| 2009-09-02 | Income | Test Transactions | 0.20 | > 0.20 | > | 23| 2009-09-02 | Income | Tester | 1.20 | > 1.40 | > | 102| 2009-09-02 | Income | Debit | -1.09 | > 0.31 | > +---------+---------------+------------+----------------------+-------+-------------------------+ > 4 rows in set (0.00 sec) > > Anyone got any tips on how to achieve this? Group with ROLLUP doesn't > really do what I need. I've tried a couple of sub queries but cant get > the output I need. > > I want to avoid storing a rolling balance into the table because this > would take a while due to the number of records and could create > problems when someone goes in and modifies a single transaction. > > Any tips would be much appreciated. > > Regards > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
