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






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

1.SUM() function returns the sum of the expression.
2.SUM() the commission
3.Sum by group
4.Some aggregate functions can still produce NULL as a result.
5.What is the total amount of mail traffic and the average size of each message?
6.What is the total population of the United States?
7.Summaries and NULL Values
8.To determine how many drivers were on the road and how many miles were driven each day
9.Finding Smallest or Largest Summary Values
10.The number of votes for each programming language is determined with SUM(IF(...))