To compute the runner's average speed at the end of each stage : SUM « Aggregate Functions « SQL / MySQL

1. SQL / MySQL
2. Aggregate Functions
3. SUM

To compute the runner's average speed at the end of each stage

      
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, t1.t,
    -> SUM(t2.km) AS 'cum. km',
    -> SEC_TO_TIME(SUM(TIME_TO_SEC(t2.t))) AS 'cum. t',
    -> SUM(t2.km)/(SUM(TIME_TO_SEC(t2.t))/(60*60)) AS 'avg. km/hour'
    -> FROM marathon AS t1, marathon AS t2
    -> WHERE t1.stage >= t2.stage
    -> GROUP BY t1.stage;
+-------+----+----------+---------+----------+--------------+
| stage | km | t        | cum. km | cum. t   | avg. km/hour |
+-------+----+----------+---------+----------+--------------+
|     1 |  5 | 00:15:00 |       5 | 00:15:00 |      20.0000 |
|     2 |  7 | 00:19:30 |      12 | 00:34:30 |      20.8696 |
|     3 |  9 | 00:29:20 |      21 | 01:03:50 |      19.7389 |
|     4 |  5 | 00:17:50 |      26 | 01:21:40 |      19.1020 |
+-------+----+----------+---------+----------+--------------+
4 rows in set (0.00 sec)

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

mysql>

   
    
    
    
    
    
  

Related examples in the same category