To calculate cumulative distance in kilometers at each stage, use a self-join

    
mysql>
mysql> CREATE TABLE marathon
    -> (
    ->  stage   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  km              INT UNSIGNED NOT NULL,  # length of stage, in km
    ->  t               TIME NOT NULL,                  # elapsed time for stage
    ->  PRIMARY KEY (stage)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO marathon (km, t)
    -> VALUES
    ->  (5,'00:15:00'),
    ->  (7,'00:19:30'),
    ->  (9,'00:29:20'),
    ->  (5,'00:17:50')
    -> ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT t1.stage, t1.km, SUM(t2.km) AS 'cum. km'
    -> FROM marathon AS t1, marathon AS t2
    -> WHERE t1.stage >= t2.stage
    -> GROUP BY t1.stage;
+-------+----+---------+
| stage | km | cum. km |
+-------+----+---------+
|     1 |  5 |       5 |
|     2 |  7 |      12 |
|     3 |  9 |      21 |
|     4 |  5 |      26 |
+-------+----+---------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table marathon;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	Determine which senders sent themselves a message is to use a self-join
2.	The self-join can be extended to display the number of days elapsed at each date
3.	Eliminating Duplicates from a Self-Join Result

To calculate cumulative distance in kilometers at each stage, use a self-join : Self Join « Join « SQL / MySQL

Related examples in the same category