SELECT from tbl 'value1' minus the 'previous value'

Question

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

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

That gives me the total amount per day that is in the table. But what I want is the incremental value compared to the previous entry (not necessarily the previous day)

The table might be something like:

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

So where my query would return: 1000,1001,1003,1008.

What I actually want is: (a number compared to previous entry - but not within the BETWEEN date range in order to start my incriments),1,2,5

Answer 1

Try this :

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

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

Answer 2

You need to join two identical ranked subqueries. I don't have a MySQL database to test this against but the form would be similar to:

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

Answer 3

This aught to 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