Calculating Differences Between Successive Rows : Join Table « Join « SQL / MySQL

1. SQL / MySQL
2. Join
3. Join Table

Calculating Differences Between Successive Rows

      
mysql>
mysql>
mysql> CREATE TABLE trip_log
    -> (
    ->  seq             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  city    VARCHAR(30) NOT NULL,   # location of stop
    ->  t               DATETIME NOT NULL,              # time of stop
    ->  miles   INT UNSIGNED NOT NULL,  # miles traveled so far
    ->  fuel    DECIMAL(6,3),                   # gas used between citees
    ->  PRIMARY KEY (seq)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO trip_log (t, city, miles, fuel)
    -> VALUES
    ->  ('2010-10-23 05:00:00', 'San Antonio, TX', 0, 0),
    ->  ('2010-10-23 09:10:00', 'Dallas, TX', 263, 11.375),
    ->  ('2010-10-23 13:40:00', 'Benton, AR', 566, 12.398),
    ->  ('2010-10-23 16:51:00', 'Memphis, TN', 745, 6.820),
    ->  ('2010-10-23 19:06:00', 'Portageville, MO', 745+133, 7.007),
    ->  ('2010-10-23 23:16:00', 'Champaign, IL', 745+419, 11.354),
    ->  ('2010-10-24 03:27:00', 'Madison, WI', 745+667, 13.016)
    -> ;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM trip_log;
+-----+------------------+---------------------+-------+--------+
| seq | city             | t                   | miles | fuel   |
+-----+------------------+---------------------+-------+--------+
|   1 | San Antonio, TX  | 2010-10-23 05:00:00 |     0 |  0.000 |
|   2 | Dallas, TX       | 2010-10-23 09:10:00 |   263 | 11.375 |
|   3 | Benton, AR       | 2010-10-23 13:40:00 |   566 | 12.398 |
|   4 | Memphis, TN      | 2010-10-23 16:51:00 |   745 |  6.820 |
|   5 | Portageville, MO | 2010-10-23 19:06:00 |   878 |  7.007 |
|   6 | Champaign, IL    | 2010-10-23 23:16:00 |  1164 | 11.354 |
|   7 | Madison, WI      | 2010-10-24 03:27:00 |  1412 | 13.016 |
+-----+------------------+---------------------+-------+--------+
7 rows in set (0.00 sec)

mysql>
mysql> SELECT t1.seq AS seq1, t2.seq AS seq2,
    -> t1.city AS city1, t2.city AS city2,
    -> t1.miles AS miles1, t2.miles AS miles2,
    -> t2.miles-t1.miles AS dist
    -> FROM trip_log AS t1, trip_log AS t2
    -> WHERE t1.seq+1 = t2.seq
    -> ORDER BY t1.seq;
+------+------+------------------+------------------+--------+--------+------+
| seq1 | seq2 | city1            | city2            | miles1 | miles2 | dist |
+------+------+------------------+------------------+--------+--------+------+
|    1 |    2 | San Antonio, TX  | Dallas, TX       |      0 |    263 |  263 |
|    2 |    3 | Dallas, TX       | Benton, AR       |    263 |    566 |  303 |
|    3 |    4 | Benton, AR       | Memphis, TN      |    566 |    745 |  179 |
|    4 |    5 | Memphis, TN      | Portageville, MO |    745 |    878 |  133 |
|    5 |    6 | Portageville, MO | Champaign, IL    |    878 |   1164 |  286 |
|    6 |    7 | Champaign, IL    | Madison, WI      |   1164 |   1412 |  248 |
+------+------+------------------+------------------+--------+--------+------+
6 rows in set (0.00 sec)

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

   
    
    
    
    
    
  

Related examples in the same category