EllBrandon EllBrandon - 3 months ago 9
MySQL Question

MySQL summary table - evaluate and adjust

I have a very simple summary table that sums up 2 fields in a table where records are collected at 15 minute intervals. So;

SELECT timevalue, SUM(value1) AS sumvalue1, SUM(value2) AS sumvalue2
FROM table
GROUP BY timevalue


returns the results i would expect;

timevalue sumvalue1 sumvalue2
-------------------------------------
16/08/2016 08:30 3000 200
16/08/2016 08:45 3200 150
16/08/2016 09:00 3100 400
16/08/2016 09:15 3300 450
16/08/2016 09:30 3400 600


My question is, is there a way to check that each sum value is never less than the previous value? and if it is return the sum from the previous timevalue? (so therefore the sumvalues are always the same or greater than the previous timevalue).

The results table should then look like this;

timevalue sumvalue1 sumvalue2
-------------------------------------
16/08/2016 08:30 3000 200
16/08/2016 08:45 3200 200
16/08/2016 09:00 3200 400
16/08/2016 09:15 3300 450
16/08/2016 09:30 3400 600


I'm guessing i need some kind of if statement? Any ideas on how to achieve this?

Many Thanks

Answer

You can do this using the user defined variable and then doing some arithmetic something as

select timevalue,sumvalue_1 as sumvalue1, sumvalue_2 as sumvalue2 from
(
 select 
 timevalue,
 if(@prev_val1 = sumvalue1 or @prev_val1 > sumvalue1,@prev_val1,sumvalue1) as sumvalue_1,
 if(@prev_val2 = sumvalue2 or @prev_val2 > sumvalue2,@prev_val2,sumvalue2) as sumvalue_2,
 @prev_val1 := sumvalue1,
 @prev_val2 := sumvalue2
 from mytable,(select @prev_val1:=0,@prev_val2:=0)x
 order by timevalue
)x
order by timevalue 

Here is a demo

create table mytable (
 timevalue datetime,
 sumvalue1 int,
 sumvalue2 int
);

insert into mytable values 
('2016-08-16 08:30:00',3000,200),
('2016-08-16 08:45:00',3200,150),
('2016-08-16 09:00:00',3100,400),
('2016-08-16 09:15:00',3300,450),
('2016-08-16 09:30:00',3400,600);


mysql> select * from mytable;
+---------------------+-----------+-----------+
| timevalue           | sumvalue1 | sumvalue2 |
+---------------------+-----------+-----------+
| 2016-08-16 08:30:00 |      3000 |       200 |
| 2016-08-16 08:45:00 |      3200 |       150 |
| 2016-08-16 09:00:00 |      3100 |       400 |
| 2016-08-16 09:15:00 |      3300 |       450 |
| 2016-08-16 09:30:00 |      3400 |       600 |
+---------------------+-----------+-----------+

Now with the query

mysql> select timevalue,sumvalue_1 as sumvalue1, sumvalue_2 as sumvalue2 from
    -> (
    ->  select 
    ->  timevalue,
    ->  if(@prev_val1 = sumvalue1 or @prev_val1 > sumvalue1,@prev_val1,sumvalue1) as sumvalue_1,
    ->  if(@prev_val2 = sumvalue2 or @prev_val2 > sumvalue2,@prev_val2,sumvalue2) as sumvalue_2,
    ->  @prev_val1 := sumvalue1,
    ->  @prev_val2 := sumvalue2
    ->  from mytable,(select @prev_val1:=0,@prev_val2:=0)x
    ->  order by timevalue
    -> )x
    -> order by timevalue;
+---------------------+-----------+-----------+
| timevalue           | sumvalue1 | sumvalue2 |
+---------------------+-----------+-----------+
| 2016-08-16 08:30:00 |      3000 |       200 |
| 2016-08-16 08:45:00 |      3200 |       200 |
| 2016-08-16 09:00:00 |      3200 |       400 |
| 2016-08-16 09:15:00 |      3300 |       450 |
| 2016-08-16 09:30:00 |      3400 |       600 |
+---------------------+-----------+-----------+