question

shridhar avatar image
shridhar asked

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
sql-server-2008group-bysum
7 comments
10 |1200

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

GPO avatar image GPO commented ·
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 Likes 0 ·
shridhar avatar image shridhar commented ·
but currently i am getting same time of data
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
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 Likes 0 ·
GPO avatar image GPO commented ·
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 Likes 0 ·
shridhar avatar image shridhar commented ·
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 Likes 0 ·
Show more comments

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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