Query for rolling totals

--=-BpsoKaSP0HkVmQlOTDgN
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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

--=-BpsoKaSP0HkVmQlOTDgN--

Re: Query for rolling totals

You can do this using a variable. Set the variable starting value with
a "query":
set @runningTotal :=3D 0

Then add the calculation to your total:
SELECT a. trans_id ,a.tran_date,b.cat_type,a.payee,a.amnt,
@runningTotal :=3D @runningTotal+a.amnt AS rollingTotal from
transactions a join categories b on a.cat_id =3D b.cat_id where
a.user_id=3D1 and a.acc_id=3D3 order by a.tran_date ASC

Brent Baisley

On Thu, Sep 3, 2009 at 1:56 PM, John
Daisley 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 =A0 =A0 | Type =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | Null | Key | Def=
ault | Extra
> |
> +-----------+----------------------+------+-----+---------+- -------------=
--+
> | trans_id =A0| int(10) unsigned =A0 =A0 | NO =A0 | PRI | NULL =A0 =A0|
> auto_increment |
> | user_id =A0 | smallint(5) unsigned | NO =A0 | MUL | NULL =A0 =A0|
> |
> | acc_id =A0 =A0| smallint(5) unsigned | NO =A0 | MUL | NULL =A0 =A0|
> |
> | tran_date | date =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | NO =A0 | =A0 =A0 | N=
ULL =A0 =A0|
> |
> | payee =A0 =A0 | varchar(25) =A0 =A0 =A0 =A0 =A0| NO =A0 | =A0 =A0 | NUL=
L =A0 =A0|
> |
> | amnt =A0 =A0 =A0| decimal(8,2) =A0 =A0 =A0 =A0 | NO =A0 | =A0 =A0 | NUL=
L =A0 =A0|
> |
> | cat_id =A0 =A0| int(10) unsigned =A0 =A0 | NO =A0 | MUL | NULL =A0 =A0|
> |
> +-----------+----------------------+------+-----+---------+- -------------=
--+
> 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> =A0SELECT a. trans_id ,a.tran_date,b.cat_type,a.payee,a.amnt from
> transactions a join categories b on a.cat_id =3D b.cat_id where
> a.user_id=3D1 and a.acc_id=3D3 order by a.tran_date ASC;
>
> Which returns a list something like this...
>
> +-----------+-----------------+--------------+-------------- -------------=
+----------+
> =A0trans_id | tran_date =A0 =A0| cat_type | payee =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 | amnt =A0|
> +-----------+-----------------+-------------+--------------- -------------=
+----------+
> =A0| =A0 =A0 =A0 =A0 =A01| 2009-08-31 | Income =A0 | Opening Balance =A0 =
=A0| =A0 0.00 |
> =A0| =A0 =A0 =A0 =A0 =A03| 2009-09-02 | Income =A0 | Test Transactions =
=A0 | =A0 0.20 |
> =A0| =A0 =A0 =A0 =A023| 2009-09-02 | Income =A0 | Tester =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0| =A0 1.20
> |
> =A0| =A0 =A0 =A0102| 2009-09-02 | Income =A0 | Debit =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 | =A0-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 =A0 =A0 | cat_type | payee =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0| amnt |
> Rolling Balance =A0 =A0 =A0|
> +---------+---------------+------------+-------------------- --+-------+--=
-----------------------+
> | =A0 =A0 =A0 =A0 =A0 1| 2009-08-31 | Income =A0 | Opening Balance | =A00=
..00 |
> 0.00 |
> | =A0 =A0 =A0 =A0 =A0 3| 2009-09-02 | Income =A0 | Test Transactions | =
=A00.20 |
> 0.20 |
> | =A0 =A0 =A0 =A0 23| 2009-09-02 | Income =A0 | Tester =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 | =A01.20 |
> 1.40 |
> | =A0 =A0 =A0 102| 2009-09-02 | Income =A0 | Debit =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0| -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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Query for rolling totals

--=-U1y++sXTROvXMP0bpb98
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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

--=-U1y++sXTROvXMP0bpb98--