MySQL cummulative sum

Question

I have table like this one:

FType | Vehicle   | Driver | Time    | QTY  
----------------------------------------------  
 ED   |   1       |  A     |20:57:51 |  5
 ED   |   1       |  A     |20:59:10 |  7
 ED   |   2       |  B     |20:58:40 |  6
 ED   |   2       |  B     |21:30:10 |  4
 ED   |   3       |  C     |21:48:33 |  1

Now I need to summarize QTY for each driver if time is < 10 minutes, if not then just print QTY

Result should be like:

FType | Vehicle   | Driver |  TotQTY
----------------------------------------------  
 ED   |   1       |  A     | 12
 ED   |   2       |  B     | 6
 ED   |   2       |  B     | 4
 ED   |   3       |  C     | 1

How to do that?

I stucked on part where I need to calculate time difference. I even have no idea how to start. — Josef, Dec 14 '16 at 20:23
I have feeling that you didn't even try to read my post/question? I know how to get difference bewteen two dates/times values when they are in the same row, but how to the same on to different rows like in my example. This is the question. — Josef, Dec 14 '16 at 20:30
Group on driver, within each group sum on time. Then filter on sum < 10 , then sum >= 10. — nicomp, Dec 14 '16 at 20:34
@nicomp The times aren't the same, he needs to find rows that are within a range of minutes from each other. — Barmar, Dec 14 '16 at 20:36
@Josef Do you mean <10 minutes between each row, or all rows <10 minutes from the first row in a sequence? — Barmar, Dec 14 '16 at 20:47
It would be much easier if you just made summaries for 10-minute periods, like `20:00-20:09`, `20:10-20:19`, `20:20-20:29`, etc. Would that work for you? — Barmar, Dec 14 '16 at 20:49
@Barmar That's not how I read it. I think an example of his query would be helpful, especially since that's kind-of the point of this site. — nicomp, Dec 14 '16 at 20:54
@nicomp Look at the times in the first two rows. They're different, but he summed the qty because the times are <10 minutes apart. — Barmar, Dec 14 '16 at 20:56
@nicomp But rows 3 and 4 aren't summed together because they're 32 minutes apart. — Barmar, Dec 14 '16 at 20:57
@Barmar I posted a link to the time diff function in MySQL and the OP accused me of not reading his question. — nicomp, Dec 14 '16 at 21:00
@Barmar i know that this is not coding service and I'm not asking to write code instead of me. I can post solution with simple select query and grouping which sumarize QTY for each driver but I never get faced with this kind of problem that I described in first post to look in different rows and search time difference below 10 minutes. — Josef, Dec 14 '16 at 21:32
You need to use a user-defined variable. `@lastTime := time`. Then in each row you can test `TIMESTAMPDIFF(time, @lastTIme) < 600` to compare the difference. — Barmar, Dec 14 '16 at 21:34

score 0 · Answer 1 · answered Dec 14 '16 at 22:33

Here's a solution based on user-defined variables as suggested by Barmar, but additionally considering the grouping of drivers. The inner query calculates which times of the same driver fall into the same group, whereas the outer query then calculates the qty-sums for each of these groups. Hope it helps.

drop table if exists test2;

create table test2 (
driver varchar(20), time time, qty int
);

insert into test2 values ('A', '20:57:51', 5);
insert into test2 values ('A', '20:59:10', 7);
insert into test2 values ('B', '20:58:40', 6);
insert into test2 values ('B', '21:30:10', 4);
insert into test2 values ('C', '21:48:33', 1);


set @driver='', @time:= '00:00:00', @group:=0; 
select driver, sum(qty) from (select @group := if (@driver = driver and TIMEDIFF(time, @time) < 600, @group, @group + 1) as samegroup, @driver := driver as driver, @time := time as time, qty from test2) groupedqty group by driver, samegroup

I don't think that there's any need for any grouping in answers of this type, — Strawberry, Dec 14 '16 at 22:57
Just to unterstand the rules of SO - what exactly is wrong with the question or with the answer? — Stephan Lechner, Dec 15 '16 at 16:16
The answer is enitrely conformant to the rules, as far as I can tell. The question could be improved by providing information in the same, considered manner as the answer. — Strawberry, Dec 15 '16 at 16:30

MySQL cummulative sum

1 Answers1