CODEWITHSUNDEEP
mysql
Sai Sai
Sai Sai

Reputation: 115

how to calculate the difference between 2 rows using mysql

I have a table MeterReading in mysql and it has the following columns

meterid bigint(4),
reading bigint(4),
date date,
time time,
consumption,
primary key(meterid,reading)

i am inserting the vlues to this table except consumption and my problem is how to update the table based on these values.

i tried the following query

update MeterReading a 
set consumption=(select reading-IFNULL((select MAX(reading) from MeterReading  where meterid=a.meterid AND (date < a.date OR date = a.date AND time < a.time )),0));

i want the result like:

  meterid | reading | date       | time     | consumption |
+---------+---------+------------+----------+-------------+
|       1 |     450 | 2012-10-05 | 06:05:05 |        450  |
|       1 |     550 | 2012-10-06 | 08:05:05 |        100  |
|       1 |     600 | 2012-10-07 | 09:05:05 |        50   |
|       1 |     700 | 2012-10-08 | 10:05:05 |        100  |

please help me

Upvotes: 0

Views: 720

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

Try this instead:

UPDATE MeterReading a 
LEFT JOIN
(
  SELECT meterid, MAX(reading) MaxReading
  FROM MeterReading
  GROUP BY meterid
) g ON g.meterid = a.meterid
SET a.consumption = IFNULL(g.MaxReading, 0) - a.reading ;

Update: Use IFNULL(g.MaxReading, 0) - a.reading or you can use the ABS(IFNULL(g.MaxReading, 0) - a.reading).

SQL Fiddle Demo

Edit: So, you need to update each composite key (meterid, reading)'s consumption value with the difference between the current reading value and the next row's reading value. If so you can do this:

UPDATE MeterReading m
INNER JOIN
(
  SELECT 
    m.*,
    @rownum := @rownum + 1 AS rank
  FROM meterreading m, (SELECT @rownum := 0) r
  ORDER BY m.date 
) t1  ON m.meterid = t1.meterid 
     AND m.reading = t1.reading
LEFT JOIN 
(
  SELECT 
    m.*,
    @rownum2 := @rownum2 + 1 AS rank
  FROM meterreading m, (SELECT @rownum2 := 0) r
  ORDER BY m.date 
) t2  ON  t1.rank - t2.rank = 1
SET m.consumption = (t1.reading - IFNULL(t2.reading, 0));

Updated SQL Fiddle Demo

This will make your table meterreading, after update, looks like:

| METERID | READING |                           DATE |                           TIME | CONSUMPTION |
-----------------------------------------------------------------------------------------------------
|       1 |     450 | October, 05 2012 02:00:00+0000 | January, 01 1970 06:05:05+0000 |         450 |
|       1 |     550 | October, 06 2012 02:00:00+0000 | January, 01 1970 08:05:05+0000 |         100 |
|       1 |     600 | October, 07 2012 02:00:00+0000 | January, 01 1970 09:05:05+0000 |          50 |
|       1 |     700 | October, 08 2012 02:00:00+0000 | January, 01 1970 10:05:05+0000 |         100 |

Note that: I used the @rownum variable to get each row's rank, and the thing is, this rank is based on the order of the Date column, so it will get the next date's reading value.

Upvotes: 1

Related Questions