show sum of column with group by

id onoff time digitalinput user distnce 1 on 16/11/2016 09:00 on a 1 2 on 16/11/2016 09:05 on a 4 3 on 16/11/2016 09:10 off a 6 4 off 16/11/2016 09:05 off a 6 5 off 16/11/2016 09:10 on a 6 6 on 16/11/2016 09:15 off b 1 7 on 16/11/2016 09:20 off a 8 8 off 16/11/2016 09:20 on b 1 9 on 16/11/2016 09:25 on b 1 10 on 16/11/2016 09:30 on b 10 11 off 17/11/2016 09:25 on a 10 12 on 17/11/2016 09:30 on b 10 13 off 17/11/2016 09:35 on a 10 my table is i want ouput like below user day totalontime totalofftime totaldigitalon totaldigitaloff totaldistnce a 16/11/2016 10 5 5 5 8 b 16/11/2016 5 0 0 10 10 a 17/11/2016 0 10 10 0 0 b 17/11/2016 0 0 0 0 0

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GPO commented · Nov 17, 2016 at 01:51 AM

I have a feeling we need to be able to relate an [onoff] value of 'on' to a subsequent 'off'. It's not clear based on the data presented, and the explanation given, how this should happen.

0 ·

shridhar commented · Nov 17, 2016 at 04:52 AM

but currently i am getting same time of data

0 ·

Kev Riley ♦♦ commented · Nov 17, 2016 at 09:30 AM

also is `distnce` cumulative? On 16/11/2016 the min value for user a is 1 and the max is 8 - suggests a 'movement' of 7. On 17/11 the min is 10 and max is 10 - 'movement' of 0. Your output suggests that for the 'first' period i need to take the movement+1, but not for subsequent?

0 ·

GPO commented · Nov 17, 2016 at 10:21 AM

In your output table, for user 'a' for day '16/11/2016', can you explain what the totalontime value of 10 is derived from?

0 ·

shridhar commented · Nov 17, 2016 at 10:59 AM

for user a on 16/11/16 check time difference between previous and next date when column onoff is on so difference of sum 16/11/2016 09:00 - 16/11/2016 09:05 is 5 + 16/11/2016 09:05- 16/11/2016 09:10 is 5 =10

0 ·

Show more comments

question

0 Answers

Write an Answer

question details

question