Count daily difference

[nobloz]

Hello all,

using mysql

I have the folowing (example) table

+------------+--------+
| date | total |
+------------+--------+
| 2009-01-08 | 150000 |
| 2009-01-09 | 151000 |
| 2009-01-10 | 156000 |
| 2009-01-11 | 154000 |
| 2009-01-12 | 153000 |
| 2009-01-13 | 157000 |
+------------+--------+

Now i want to make a query that counts the diffrence with the day befor.
I has looked ad some running total query's, but cant find a way to creat a correct query.

The results should look like this:

+------------+--------+-------+
| date | total | diff |
+------------+--------+-------+
| 2009-01-08 | 150000 | |
| 2009-01-09 | 151000 | 1000 | -> 151000 - 150000
| 2009-01-10 | 156000 | 5000 | -> 156000 - 151000
| 2009-01-11 | 154000 | -2000 | -> 154000 - 156000
| 2009-01-12 | 153000 | -1000 | -> 153000 - 154000
| 2009-01-13 | 157000 | 4000 | -> 157000 - 153000
+------------+--------+-------+

( the -> ...... are the counting that must be done and isnt part of the result)

I will be very happy if someone can help me with this.

Thx,

Nobloz

[nobloz]

Found the solution for this. thx anyway.

SELECT t1.date 
,      t1.total 
,      t1.total 
   - ( SELECT t2.total 
       FROM   tabel t2 
       WHERE  t2.date = DATE_SUB( t1.date, INTERVAL 1 DAY ) 
     ) diff 
FROM   tabel t1;