Subtracting row from another in MySQL by date

Question

I've been busy with some MySQL where I've got several rows of data, and I want to subtract a row from the previous row and have it repeat all the way down. Here is my table:

id    day          vabaraha   tagatis   kasutajad   slot2    
17    2017-08-01   592698     80477     108949      33821891    
18    2017-08-02   584221     80485     109124      33880051    
19    2017-08-03   591121     77525     109257      33920524    
20    2017-08-04   596247     77111     109269      33921504    
21    2017-08-04   599451     80216     109448      33950284    
22    2017-08-05   600504     79801     109542      33969574    
23    2017-08-06   606778     78750     109618      33982244    
24    2017-08-07   634498     72758     109817      34023524    
25    2017-08-08   621859     79007     109962      34099260    
26    2017-08-09   626617     77321     110092      34166030    

I want echo with php something like this:

id    day          vabaraha   tagatis   kasutajad   slot2      DIFF
20    2017-08-04   596247     77111     109269      33921504   980
19    2017-08-03   591121     77525     109257      33920524   40473
18    2017-08-02   584221     80485     109124      33880051 

I tried some LEFT JOIN but cant figure it out. My code:

  if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

    $mysqltime = date ("Ymd");
    $agotime = date('Ymd', strtotime('-1 days'));
     //echo  $mysqltime. " <br> ";
     //echo $agotime. "<br> ";


   $sql = "SELECT c.day, c.slot2, COALESCE(c.slot2-a.slot2,0) diff
FROM Omaraha c
LEFT JOIN Omaraha a ON a.day < c.day
LEFT JOIN Omaraha b ON a.day < b.day AND b.day < c.day
WHERE b.day IS NULL;";

   mysql_select_db('investee_Omaraha');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   while($row = mysql_fetch_assoc($retval)) {
      echo         
        "EMP NAME : {$row['newAmount']} <br> ".
         "EMP SALARY : {$row['vabaraha']} <br> ".
         "EMP SALARY : {$row['tagatis']} <br> ".
         "EMP SALARY : {$row['slot2']} <br> ".
         "--------------------------------<br>";
   }

   echo "<br><br>!! Fetched data successfully !!\n";

   mysql_close($conn);

Thank you for helping me out.

Answer 1

You may want to take a look at another post that does counting between rows sequentially by whatever criteria using MySQL @variable usage like an in-line program.

Another to show +/- even working between group by that might help you along

I don't understand your basis of why those particular IDs in reverse order, but you can order your data by whatever your basis is to get computations started, then the @variables act like an inline program and available with its value for the next record being processed. The two examples I provided show good examples of this being applied.

If you can clarify your context of what the purpose is for the aggregations, I can better post a more detailed example. Why are you starting with IDs 20, 19, 18, 17, then the IDs go back up 21, 22, 23, etc. What is the common element you are trying to work out.

From your sample description, I have applied a query to try using the sqlvariables...

SELECT
      pq.*
   from
      ( SELECT 
              o.id,
              o.day,
              o.vabaraha,
              o.tagatis,
              o.kasutajad,
              IF( @lastSlot2 = 0, 0, o.slot2 - @lastSlot2 ) DayDifference,
              @lastSlot2 := o.slot2
           from
              Omaraha o,
              ( select @lastSlot2 := 0 ) sqlvars
           order by
              o.day ) pq
   order by
      pq.Day descending     

So, here, the sqlvars query just prepares 2 variables starting with no previous "LastSlot2" value as it represents the beginning of the list process. The order of the data by day pre-sorts in date order. I am first getting the if() to check. If no previous value for slot2, I just return 0. This will be for the first returned earliest date first. After that line is considered, I NOW take t he slot2 and store it into the "@lastSlot2" variable. Now, the next record through identifies @lasSlot2 HAS a value, so it can take the current slot and detect the difference of the new record. Every subsequent record has the @lastSlot2 = whatever that record's slot2 was for the next cycle.

Since the inner query is ordered by day, it processes them all. But now, you want the reverse, so I had to wrap that query into its own and select everything from that, but now, ordered by date reversed to have the most recent date at the top of the list.