Thank you Brent, much appreciated!
On Thu, 2009-09-03 at 14:12 -0400, Brent Baisley wrote: > 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 > >
