Skip to content
Advertisement

Mysql subtract and sum

I have a below table in mysql.

The same is in my SQL Fiddle

By using this query SELECT * FROM mdc_data d WHERE d.msn ='00209706' ORDER BY d.start_data_date_time ASC I am getting

What I want to do?

The first value at 2020-09-10 00:00:00 is the start value.

I want to take out the consumption against each meter. For example meter number is 00209706. For getting consumption for WAPDA the following algorithm is required.

Similarly for generator

Expected Output

Advertisement

Answer

I think you want lag(), then aggregation:

I find that it would make more sense to substract the consumption over rows that belong to the same source – but the above is what you asked for.

Demo on DB Fiddle:

msn      | units | incoming_source | date      
:------- | ----: | :-------------- | :---------
00209706 |    19 | Generator       | 2020-09-10
00209706 |    33 | WAPDA           | 2020-09-10
00209707 |    20 | Generator       | 2020-09-10
00209707 |    28 | WAPDA           | 2020-09-10
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement