SELECT of tbl 'value1' minus 'previous value'

Question

SELECT      totalAmount
FROM        tbl
BETWEEN     'date1' AND 'date2'

GROUP BY    DATE(date created) 
ORDER BY    DATE(date created)

This gives me the total amount for the day, which is in the table. But what I want is an incremental value compared to the previous record (not necessarily the previous day).

A table might look something like this:

totalAmount    |    date created
---------------------------------
1000           |    1st Jan
1001           |    2nd Jan
1003           |    3rd Jan
1008           |    15th Jan

So, when my request returns: 1000,1001,1003,1008.

I really want: (a number compared to the previous entry - but not within the BETWEEN date range to start my attempts), 1,2,5

Answer 1

This should do it:

SELECT IFNULL((t1.totalamount - t2.totalamount),0) as diff, t1.date_created
FROM view t1 LEFT OUTER JOIN view t2 
 ON t2.date = (SELECT MAX(date) FROM view WHERE date < t1.date)
WHERE t1.date_created BETWEEN 'date1' AND 'date2'
ORDER BY date_created

Where "view" is this query:

SELECT date_created, SUM(totalamount) FROM tbl GROUP BY date_created

Answer 2

Try the following:

SELECT      totalAmount
FROM        tbl
BETWEEN     'date1' AND 'date2'

GROUP BY    DATE(date created) 
ORDER BY    totalAmount DESC, DATE(date created)

Answer 3

. MySQL , :

select t1.TotalAmount, t1.DateCreated, 
    t1.TotalAmount - t2.TotalAmount as 'Delta'
from
(select rownum, TotalAmount
 from tbl
 order by DateCreated) t1
left outer join
(select rownum, TotalAmount
 from tbl
 order by DateCreated) t2
 on t1.rownum = t2.rownum + 1
 order by t1.rownum