SQL Subtract two rows from each other in same column to get a result

Question

I am running many energy readers which collects data with intervals of 5min. To get power used between two time stamps, I need to subtract older data from the newer data.

I am new to SQl, so I would like to ask what SQL syntax do I need to use to get a total sum between two Values.

Database Structure example:

Thank you very much.

Answer 1

You would typically use window function lead() or lag() - if your database supports that.

The following query puts on each row the difference with the previous value of the same equipment:

select
    t.*,
    t_energy_a - lag(t_energy_a) over(partition by meter_id order by dod) diff
from mytable t