1

I want to create a column in mysql workbench where there is a sum of the duration of a behaviour when multiple columns (e.g condition, trial, subject, behaviour type...) are the same.

I am relatively new to working in sql and have so far mostly just used it for extracting data. Is this something that is possible? If so how do I go about it?

Thanks :)

Edit - The data would be something like this 

Condition  Trial   Group  Subject  Behaviour  Duration   **TotalDuration**  
np         1       XX     GBF132   interact   00:00:42  
np         1       XX     GBF132   interact   00:00:17   
yp         1       ZZ     HJR543   interact   00:01:03            
yp         1       ZZ     HJR543   interact   00:00:26

So there could be multiple interactions within a trial by the same individual and I would like to determine the overall interaction duration per trial.

But I have multiple subjects/behaviours/Groups per trial and condition so need a way to easily calculate the total duration of each behaviour when the condition, trial, group, subject, behaviour are the same .

I hope this makes it clearer!

Isabel
  • 37
  • 6
  • 2
    Could you please provide an example of data you have and an example of result you expect from it? I just realized it's kind of unclear what values do you you want to summarize. – Mikhail Antonov Aug 30 '18 at 11:31
  • Thanks for your reply! I've edited the post to hopefully make it a bit clearer :) – Isabel Aug 30 '18 at 13:42

1 Answers1

0

AFAIK in recent MySQL versions they've added support for window functions. It seems that those functions can be used to solve your problem. I didn't test it, but it would be used somewhat like that:

SELECT
  `condition`, `trial`, `group`, `subject`, `behaviour`,
  `duration`,
  SEC_TO_TIME(SUM(TIME_TO_SEC(`duration`))) OVER (PARTITION BY condition`, `trial`, `group`, `subject`, `behaviour`) AS timeSum
FROM `tests`;

Anyway, for older versions of MySQL the following might work for you:

SELECT
   `condition`,
   `trial`,
   `group`,
   `subject`,
   `behaviour`,
   `duration`, SEC_TO_TIME(SUM(TIME_TO_SEC(`duration`)))
FROM `tests`
GROUP BY `condition`, `trial`,`group`, `subject`,`behaviour`;

but that way you would kind of lose the "details". To replicate the way window functions work you would need to use something like:

SELECT `t1`.`condition`, `t1`.`trial`,`t1`.`group`, `t1`.`subject`,`t1`.`behaviour`, `t2`.`duration`,`t1`.`totalTime` FROM (SELECT
  `condition`,
  `trial`,
  `group`,
  `subject`,
  `behaviour`,
  `duration`, SEC_TO_TIME(SUM(TIME_TO_SEC(`duration`))) AS totalTime
FROM `tests` AS subt1
GROUP BY `condition`, `trial`,`group`, `subject`,`behaviour`) AS t1
JOIN `tests` AS t2 USING (`condition`, `trial`,`group`, `subject`,`behaviour`);

Here's the code snippet I've played around: http://sqlfiddle.com/#!9/e002f2/12/0

Mikhail Antonov
  • 1,297
  • 3
  • 21
  • 29
  • Why do you assume that `condition` and `trial` should be equal to true (=1)? – AlexL Aug 30 '18 at 12:30
  • Because the question title states "...when multiple other columns are true". Anyway, it's not hard to change condition to mutual equality. And now when author edited her question my answer lost it's sense completely :) – Mikhail Antonov Aug 31 '18 at 05:14
  • Awesome!! Thank you so much! The third example worked perfectly for me. Quick question how can I format the time for the 'totalTime' column as my times are hh:mm:ss.000 and at the moment I'm losing the milliseconds. I have tried: TIME_FORMAT(`t1`.`TotalTime`, '%H:%i:%s.%f') AS TotalDuration But it is just 0's for the milliseconds Thanks :) – Isabel Aug 31 '18 at 10:45
  • I did a little investigation and I'm sorry but handling time with millisecond precision in MySQL is a complex challenge by itself (https://stackoverflow.com/questions/2572209/why-doesnt-mysql-support-millisecond-microsecond-precision). SEC_TO_TIME and TIME_TO_SEC used in my answer can handle time only with lower precision. – Mikhail Antonov Aug 31 '18 at 11:09
  • BTW which version of MySQL are you using? It seems to me things have changed since 5.6 so maybe there's a solution. And do you see your durations (not summed up) with milliseconds? Which data type are they stored in? – Mikhail Antonov Aug 31 '18 at 11:13
  • 1
    I'm using mySQL workbench 6.3. Yes, for my durations they have milliseconds with the column datatype set as TIME(3). Thanks for your help! – Isabel Aug 31 '18 at 13:14
  • I am able to keep the milliseconds if I don't use the time_to_sec/sec_to_time and just use sum, while still getting the same output :) – Isabel Sep 05 '18 at 13:24