Shows the daily and cumulative precipitation for each day : Join Table « Join « SQL / MySQL

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

Shows the daily and cumulative precipitation for each day

      
mysql>
mysql> CREATE TABLE rainfall
    -> (
    ->  date    DATE NOT NULL,
    ->  precip  FLOAT(10,2) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO rainfall (date, precip)
    ->  VALUES
    ->          ('2002-06-01', 1.5),
    ->          ('2002-06-02', 0),
    ->          ('2002-06-03', 0.5),
    ->          ('2002-06-04', 0),
    ->          ('2002-06-05', 1.0)
    -> ;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM rainfall;
+------------+--------+
| date       | precip |
+------------+--------+
| 2002-06-01 |   1.50 |
| 2002-06-02 |   0.00 |
| 2002-06-03 |   0.50 |
| 2002-06-04 |   0.00 |
| 2002-06-05 |   1.00 |
+------------+--------+
5 rows in set (0.00 sec)

mysql>
mysql> SELECT t1.date, t1.precip AS 'daily precip',
    -> SUM(t2.precip) AS 'cum. precip'
    -> FROM rainfall AS t1, rainfall AS t2
    -> WHERE t1.date >= t2.date
    -> GROUP BY t1.date;
+------------+--------------+-------------+
| date       | daily precip | cum. precip |
+------------+--------------+-------------+
| 2002-06-01 |         1.50 |        1.50 |
| 2002-06-02 |         0.00 |        1.50 |
| 2002-06-03 |         0.50 |        2.00 |
| 2002-06-04 |         0.00 |        2.00 |
| 2002-06-05 |         1.00 |        3.00 |
+------------+--------------+-------------+
5 rows in set (0.00 sec)

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

   
    
    
    
    
    
  

Related examples in the same category