MySQL – running totals/determine row position

[adsense id=”0514458240″ width=”468″ height=”60″]

This post demonstrates how to get running totals of row positions on a query. Firstly let us look at a query that permits us to order entries and then give them a position starting from 1 to the number of entries in the returned query.

To give a unique ordered identifier we will use user session variables @var. These are maintain within the scope of the user session only and are particularly good at keeping track of field values between queries and also within queries as we shall soon see.

We have a table that contains an event_id and prize money fields. We want to order the information by event_id and by prize money descending. We also want to introduce a position field which contains the position of the row within the returned query set of rows.

mysql> select money, event_id from prize order by event_id, money desc;
+-------+----------+
| money | event_id |
+-------+----------+
|   120 |      101 | 
|    60 |      101 | 
|    30 |      101 | 
|    10 |      102 | 
|     5 |      102 | 
|     2 |      102 | 
|   100 |      103 | 
|    60 |      103 | 
|    40 |      103 | 
|  1000 |      401 | 
+-------+----------+
10 rows in set (0.00 sec)

From this we have correctly ordered the rows but we are missing the position field. In this case we need to set the user session variable and this will be used within our query as follows:

mysql> set @position = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select money, event_id, @position:=@position+1 as position from prize order by event_id, money desc;
+-------+----------+----------+
| money | event_id | position |
+-------+----------+----------+
|   120 |      101 |        1 | 
|    60 |      101 |        2 | 
|    30 |      101 |        3 | 
|    10 |      102 |        4 | 
|     5 |      102 |        5 | 
|     2 |      102 |        6 | 
|   100 |      103 |        7 | 
|    60 |      103 |        8 | 
|    40 |      103 |        9 | 
|  1000 |      401 |       10 | 
+-------+----------+----------+
10 rows in set (0.00 sec)

Be aware that if you re-run the query you need to reset the value of @position back to 0 otherwise the next number returned will be 11 in this case.

mysql> select money, event_id, @position:=@position+1 as position from prize order by event_id, money desc;
+-------+----------+----------+
| money | event_id | position |
+-------+----------+----------+
|   120 |      101 |       11 | 
|    60 |      101 |       12 | 
|    30 |      101 |       13 | 
|    10 |      102 |       14 | 
|     5 |      102 |       15 | 
|     2 |      102 |       16 | 
|   100 |      103 |       17 | 
|    60 |      103 |       18 | 
|    40 |      103 |       19 | 
|  1000 |      401 |       20 | 
+-------+----------+----------+
10 rows in set (0.00 sec)

So far so good. But what happens if we want to keep a running total of the prize money group by event_id. In other words we want to show the event_id, the sum of the prize money at that event and a running total. You may think that this is done as follows:

mysql> set @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select event_id, sum(money) as money, @total := @total + sum(money) from prize group by event_id order by event_id asc;
+----------+-------+-------------------------------+
| event_id | money | @total := @total + sum(money) |
+----------+-------+-------------------------------+
|      101 |   210 |                           210 | 
|      102 |    17 |                            17 | 
|      103 |   200 |                           200 | 
|      401 |  1000 |                          1000 | 
+----------+-------+-------------------------------+
4 rows in set (0.00 sec)

This is not the correct result, we should be expecting the entries to be added. However, this feature does not work with aggregate functions such as sum. In this case here what we need to do is create a subquery to return the individual rows and then we track the running totals based on that.

mysql> set @total = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select event_id, summoney, @total:=@total+summoney
    -> from (select event_id, sum(money) summoney from prize group by event_id)
    -> tot
    -> order by event_id desc;
+----------+----------+-------------------------+
| event_id | summoney | @total:=@total+summoney |
+----------+----------+-------------------------+
|      401 |     1000 |                    1000 | 
|      103 |      200 |                    1200 | 
|      102 |       17 |                    1217 | 
|      101 |      210 |                    1427 | 
+----------+----------+-------------------------+
4 rows in set (0.01 sec)

That completes this mini tutorial. Keep following us on twitter, facebook or at this site.