Below is my query.
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`) AS kwh_diff
FROM mdc_node n
INNER JOIN `mdc_meters_data` m ON n.`customer_id` = m.`cust_id`
WHERE n.`lft` = 5 AND n.`icon` NOT IN ('folder')
AND m.`data_date_time` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
Which gives me below result
I want to sum up the kwh_diff
and to show only one-row record not multiple like below
name customer_id msn sum_kwh_diff
Zeeshan 37010114711 4A60193390663 4.5
I have tried to do the following
SUM(m.kwh - LAG(m.kwh) OVER(PARTITION BY n.`customer_id` ORDER BY m.`data_date_time`)) AS sum_kwh_diff
and got Error Code: 4074 Window functions can not be used as arguments to group functions.
You want to sum the differences between consecutive rows.
Say, for example, that you have these values for the column kwh
:
kwh
---
10
12
14
17
25
32
so the differences are:
kwh_diff
--------
0
12-10
14-12
17-14
25-17
32-25
The sum of these differences is equal to 32-10
which is:
the diffference between the last value and the first value
So what you need is window function FIRST_VALUE()
to obtain these values:
SELECT DISTINCT n.`name`, n.`customer_id`, m.`msn`,
FIRST_VALUE(m.kwh) OVER (PARTITION BY n.`customer_id` ORDER BY m.`data_date_time` DESC) -
FIRST_VALUE(m.kwh) OVER (PARTITION BY n.`customer_id` ORDER BY m.`data_date_time` ASC) AS kwh_diff
FROM mdc_node n
INNER JOIN `mdc_meters_data` m ON n.`customer_id` = m.`cust_id`
WHERE n.`lft` = 5 AND n.`icon` NOT IN ('folder')
AND m.`data_date_time` BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
and no subquery or aggregation is needed.
I kept in my code PARTITION BY n.customer_id
because you use it in your code, although you may need PARTITION BY n.customer_id, m.msn
.