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
