SQL SUM values in declared column into total and group by id

Hello people of the internet.

Once again I seek your advice.

I have multiple meter readers collecting power consumption every 5 minutes and storing to MS SQL Database. I execute SQL query to get a days worth of data from all meter readers and check how much power it has used every five minutes and returning as ‘

Consumed

‘ result. This is SQL syntax that I use to get data:

SELECT Dep_Name, DoD, 
Convert(FLOAT,T_Energy_A) - lag(T_Energy_A) 
OVER(PARTITION BY Meter_ID ORDER BY DoD) AS Consumed
FROM Meter_Data
WHERE DoD>= DATEADD(HOUR,-1, GETDATE()) 
ORDER BY dateOfData DESC

The result I Get is this:

Dep_Name           DoD               Consumed
Bath 6  2020-04-23 17:50:05.253 181
RPi Bath 5  2020-04-23 17:50:04.863 89
RPi Bath 4  2020-04-23 17:50:04.497 136
RPi Bath 3  2020-04-23 17:50:04.113 14
Solder Bath 6   2020-04-23 17:50:03.727 220
Solder Bath 5   2020-04-23 17:50:03.367 1261
Solder Bath 4   2020-04-23 17:50:02.997 409
Solder Bath 3   2020-04-23 17:50:02.590 0
Reflow 10   2020-04-23 17:50:02.287 440
Reflow 9    2020-04-23 17:50:02.197 244
Reflow 8    2020-04-23 17:50:02.020 361
Cam2    2020-04-23 17:50:01.787 180
Reflow 7    2020-04-23 17:50:01.457 366
Reflow 6    2020-04-23 17:50:01.030 506
Reflow 5    2020-04-23 17:50:00.837 453
Reflow 4    2020-04-23 17:50:00.770 280
Reflow 3    2020-04-23 17:50:00.697 243
Reflow 2    2020-04-23 17:50:00.630 389
Bath 6  2020-04-23 17:45:05.907 180
RPi Bath 5  2020-04-23 17:45:05.627 89
RPi Bath 4  2020-04-23 17:45:05.213 137
RPi Bath 3  2020-04-23 17:45:04.967 13
Solder Bath 6   2020-04-23 17:45:04.580 240
Solder Bath 5   2020-04-23 17:45:04.203 1254
Solder Bath 4   2020-04-23 17:45:03.967 413
Solder Bath 3   2020-04-23 17:45:03.807 0
Reflow 10   2020-04-23 17:45:03.560 493
Reflow 9    2020-04-23 17:45:03.163 245
Reflow 8    2020-04-23 17:45:02.807 360
Cam2    2020-04-23 17:45:02.433 179
Reflow 7    2020-04-23 17:45:02.063 379
Reflow 6    2020-04-23 17:45:01.473 505
Reflow 5    2020-04-23 17:45:01.383 450
Reflow 4    2020-04-23 17:45:01.157 279
Reflow 3    2020-04-23 17:45:00.780 242
Reflow 2    2020-04-23 17:45:00.657 387
Bath 6  2020-04-23 17:40:06.247 181
RPi Bath 5  2020-04-23 17:40:05.853 89
RPi Bath 4  2020-04-23 17:40:05.477 137
RPi Bath 3  2020-04-23 17:40:05.060 14
Solder Bath 6   2020-04-23 17:40:04.667 197
Solder Bath 5   2020-04-23 17:40:04.283 1212
Solder Bath 4   2020-04-23 17:40:03.887 385
Solder Bath 3   2020-04-23 17:40:03.547 0
Reflow 10   2020-04-23 17:40:03.280 495
Reflow 9    2020-04-23 17:40:03.063 244
Reflow 8    2020-04-23 17:40:02.600 361
Cam2    2020-04-23 17:40:02.303 178
Reflow 7    2020-04-23 17:40:01.943 353
Reflow 6    2020-04-23 17:40:01.500 508
Reflow 5    2020-04-23 17:40:01.280 449
Reflow 4    2020-04-23 17:40:01.040 280
Reflow 3    2020-04-23 17:40:00.687 241
Reflow 2    2020-04-23 17:40:00.620 387
Bath 6  2020-04-23 17:35:06.150 181
RPi Bath 5  2020-04-23 17:35:05.760 89
RPi Bath 4  2020-04-23 17:35:05.303 137
RPi Bath 3  2020-04-23 17:35:04.960 14
Solder Bath 6   2020-04-23 17:35:04.597 213
Solder Bath 5   2020-04-23 17:35:04.360 1178

Now I want to sum all ‘Consumed’ column data to as totalPower for each department, and group by Dep_Name in 1 day interval? Something Like this:

Dep_Name      totalPower
Bath 6          123
RPi Bath 5      321
RPi Bath 4      1566
RPi Bath 3      1134
Solder Bath 6   ...
Solder Bath 5   ...
Solder Bath 4   ...
...             ...

Is it possible to do so? if Yes, how can I do it?

I am new to all this, so I don’t know.

Answer

If T_Energy_A is always increasing, you can just do:

select dep_name, max(t_energy_a) - min(t_energy_a) totalpower
from meter_data
where dod >= dateadd(hour,-1, getdate()) 
group by dep_name

If you want this on a day by day basis:

select cast(dod as date) dod_day, dep_name, max(t_energy_a) - min(t_energy_a) totalpower
from meter_data
group by cast(dod as date), dep_name
order by dod_day, dep_name

Otherwise, you can just add one level of aggregation to your existing query:

select dep_name, cast(dod as date) dod_day, sum(consumed) totalpower
from (
    select 
        dep_name, 
        dod, 
        t_energy_a - lag(t_energy_a) over(partition by meter_id order by dod) as consumed
    from meter_data
) t
group by cast(dod as date), dep_name
order by dod_day, dep_name