CODEWITHSUNDEEP
mysqlsql
Moeez
Moeez

Reputation: 478

MySQL take difference between two fields

I have a query from which I am getting data.

SELECT n.`name`,n.`customer_id`,m.`msn`, m.kwh FROM mdc_node n
INNER JOIN `mdc_meters_data` m ON n.`customer_id` = m.`cust_id`
WHERE n.`lft` = 3 AND n.`icon` NOT IN ('folder')
AND m.`data_date_time` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

Output

enter image description here

Now I want to do is to subtract the kwh value like kwh[2] - kwh[1], kwh[3] - kwh[2], kwh[4] - kwh[3]..... kwh[n+1] - kwh[n]

Any help would be highly appreciated.

Upvotes: 0

Views: 41

Answers (1)

rinz1er
rinz1er

Reputation: 449

LAG() window function can be used for this problem as shown below,

SELECT n.`name`,n.`customer_id`,m.`msn`, m.kwh,
       m.kwh - LAG(m.kwh) OVER(PARTITION BY n.`customer_id` ORDER BY m.`data_date_time`)
FROM mdc_node n
INNER JOIN `mdc_meters_data` m ON n.`customer_id` = m.`cust_id`
WHERE n.`lft` = 3 AND n.`icon` NOT IN ('folder')
AND m.`data_date_time` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()

Upvotes: 1

Related Questions