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

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

From stackoverflow

• Try this :

  SELECT      totalAmount
  FROM        tbl
  BETWEEN     'date1' AND 'date2'
  
  GROUP BY    DATE(date created) 
  ORDER BY    totalAmount DESC, DATE(date created)
  

  ed209 : I think you misunderstood the question

• 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
  

  Eric Petroelje : I don't think mysql has an equivilent of "rownum" in SQL server, so this wouldn't work in his case.

  Jamie Ide : SQL Server doesn't have it, it has a row_number() function. I quickly googled it and thought the function was in MySQL. There may be a workaround: http://snippets.aktagon.com/snippets/156-MySQL-rownum-equivalent

• 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
  

  ed209 : I'm totally lost, I'm sure you're right but I can't work it out ;) thanks for answering though.

  Eric Petroelje : no problem :) The idea is that you are joining your table to itself to get two copies of the data (t1 & t2). The join is set up so that the second copy will contain the row with the largest date that is still less than the date on the first copy (MAX(DATE) .. WHERE date < t1.date). That help?

  ed209 : yes - that helps, I get the concept now. Will try it out, thanks.