CODEWITHSUNDEEP
mysqlsqldatetimesumwindow-functions
Faisal Qayyum
Faisal Qayyum

Reputation: 132

Mysql subtract and sum

I have a below table in mysql.

DROP TABLE IF EXISTS `mdc_data`;

CREATE TABLE `mdc_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tariff_id` int(11) DEFAULT NULL,
`msn` varchar(100) DEFAULT NULL,
`cust_id` varchar(100) DEFAULT NULL,
`kwh_t_max` varchar(100) DEFAULT NULL,
`start_data_date_time` datetime DEFAULT NULL,
`end_data_date_time` datetime DEFAULT NULL,
`incoming_source` varchar(100) DEFAULT NULL,
`t_type` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

 /*Data for the table `mdc_data` */

insert  into `mdc_data`(`id`,`tariff_id`,`msn`,`cust_id`,`kwh_t_max`,`start_data_date_time`,`end_data_date_time`,`incoming_source`,`t_type`) values (1,12,'00209706','37010114710','281','2020-09-10 00:40:00','2020-09-10 01:10:00','WAPDA','Slab Based'),(2,12,'00209707','37010114712','103','2020-09-10 00:40:00','2020-09-10 01:10:00','WAPDA','Slab Based'),(3,12,'00209706','37010114710','297','2020-09-10 02:00:00','2020-09-10 02:30:00','WAPDA','Slab Based'),(4,12,'00209707','37010114712','119','2020-09-10 02:00:00','2020-09-10 02:30:00','WAPDA','Slab Based'),(5,12,'00209706','37010114710','312','2020-09-10 03:20:00','2020-09-10 03:50:00','WAPDA','Slab Based'),(6,12,'00209707','37010114712','135','2020-09-10 03:20:00','2020-09-10 03:50:00','WAPDA','Slab Based'),(7,12,'00209706','37010114710','285','2020-09-10 01:20:00','2020-09-10 01:50:00','Generator','Slab Based'),(8,12,'00209707','37010114712','107','2020-09-10 01:20:00','2020-09-10 01:50:00','Generator','Slab Based'),(9,12,'00209706','37010114710','304','2020-09-10 02:40:00','2020-09-10 03:10:00','Generator','Slab Based'),(10,12,'00209707','37010114712','127','2020-09-10 02:40:00','2020-09-10 03:10:00','Generator','Slab Based'),(11,12,'00209706','37010114710','320','2020-09-10 04:00:00','2020-09-10 04:30:00','Generator','Slab Based'),(12,12,'00209707','37010114712','143','2020-09-10 04:40:00','2020-09-10 05:10:00','Generator','Slab Based'),(13,12,'00209706','37010114710','268','2020-09-09 23:20:00','2020-09-10 00:00:00','Generator','Slab Based'),(14,12,'00209707','37010114712','95','2020-09-09 23:20:00','2020-09-10 00:00:00','Generator','Slab Based');

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

 id|tariff_id|msn     | cust_id   |kwh_t_max|start_data_date_time|end_data_date_time |incoming_source|t_type    |
=================================================================================================================
 13|    12   |00209706|37010114710|    268  |2020-09-09 23:20:00 |2020-09-10 00:00:00|Generator      |Slab Based|
  1|    12   |00209706|37010114710|    281  |2020-09-10 00:40:00 |2020-09-10 01:10:00|WAPDA          |Slab Based|
  7|    12   |00209706|37010114710|    285  |2020-09-10 01:20:00 |2020-09-10 01:50:00|Generator      |Slab Based|
  3|    12   |00209706|37010114710|    297  |2020-09-10 02:00:00 |2020-09-10 02:30:00|WAPDA          |Slab Based|
  9|    12   |00209706|37010114710|    304  |2020-09-10 02:40:00 |2020-09-10 03:10:00|Generator      |Slab Based|
  5|    12   |00209706|37010114710|    312  |2020-09-10 03:20:00 |2020-09-10 03:50:00|WAPDA          |Slab Based|
 11|    12   |00209706|37010114710|    320  |2020-09-10 04:00:00 |2020-09-10 04:30:00|Generator      |Slab Based|
....

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.

281 - 268 = 13 | 297 - 285 = 12 | 312 - 304 = 8  |
// Adding the difference value 
13+12+8 = 33 => is the consumption against `WAPDA`

Similarly for generator

 285 - 281 = 4 | 304 - 297 = 7 | 320 - 312 = 8  |
// Adding the difference value 
4+7+8 = 19=> is the consumption against `Generator`

Expected Output

 msn    | units | incoming_source | date       |    
====================================================
00209706| 33    | WAPDA           | 2020-09-10 |
00209706| 19    | Generator       | 2020-09-10 | 

....

Upvotes: 0

Views: 44

Answers (1)

GMB
GMB

Reputation: 222482

I think you want lag(), then aggregation:

select msn, sum(kwh_t_max - lag_kwh_t_max) units, incoming_source, date(end_data_date_time) date
from (
    select 
        t.*, 
        lag(kwh_t_max) over(partition by msn order by end_data_date_time) lag_kwh_t_max
    from mytable t
) t
group by msn, incoming_source, date(end_data_date_time)

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

Upvotes: 1

Related Questions