MySQL – Subtracting value from previous row, group by

by

Working with MySQL variables is great, its like inline program variable assignments. First, the FROM clause “declares” the @ variables for you, defaulting to blank. Then query the records in the expected order you want them. It makes a single pass through the data instead of via repeated subqueries which can be time intensive.

For each row read, compare the @lastSN with the SN of the current record. If different, always return 0. If it IS the same, compute the simple difference. Only AFTER that compare is done, set the @lastSN and @lastValue equal to that of the current record for the next records comparison.

select
      EL.SN,
      EL.Date,
      EL.Value, --remove duplicate alias
      if( @lastSN = EL.SN, EL.Value - @lastValue, 0000.00 ) as Consumption,
      @lastSN := EL.SN,
      @lastValue := EL.Value
   from
      EnergyLog EL,
      ( select @lastSN := 0,
               @lastValue := 0 ) SQLVars
   order by
      EL.SN,
      EL.Date

More Related Contents:

  1. Getting the SUM() of claps and at the same time counting bookmarks in one query
  2. mysql – I dont understand what this means, can anyone explain each line of the script to me? [closed]
  3. How to filter SQL results in a has-many-through relation
  4. Return row only if value doesn’t exist
  5. How to delete from multiple tables in MySQL?
  6. INSERT with SELECT
  7. How to get list of dates between two dates in mysql select query [duplicate]
  8. difference between primary key and unique key
  9. Why does MySQL allow “group by” queries WITHOUT aggregate functions?
  10. Cumulative sum over a set of rows in mysql
  11. How to set a maximum execution time for a mysql query?
  12. Set NOW() as Default Value for datetime datatype?
  13. SQL keys, MUL vs PRI vs UNI
  14. Add Auto-Increment ID to existing table?
  15. MySQL SELECT DISTINCT multiple columns
  16. How to copy a row and insert in same table with a autoincrement field in MySQL?
  17. SQL DELETE with JOIN another table for WHERE condition
  18. mysql trigger stored trigger is already used by statement which invoked stored trigger
  19. Error Dropping Database (Can’t rmdir ‘.test\’, errno: 17)
  20. Using group by on two fields and count in SQL
  21. Can MySQL convert a stored UTC time to local timezone?
  22. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  23. mysql update query with sub query
  24. What is the difference between the backtick and the square bracket in SQL statements?
  25. MySQL CONCAT returns NULL if any field contain NULL
  26. Does Order of Fields of Multi-Column Index in MySQL Matter
  27. Integrity constraint violation: 1452 Cannot add or update a child row:
  28. Alternative to using LIMIT keyword in a SubQuery in MYSQL
  29. What does SQL Select symbol || mean?
  30. MySQL optimizing INSERT speed being slowed down because of indices

Leave a Comment