CODEWITHSUNDEEP
mysql
ojdajuiceman
ojdajuiceman

Reputation: 371

MySQL calculate only the row-differences for rows in the same group

I have the following table (call it trans):

issue_id:   state_one:  state_two:  timer:      
1           A           B           1
1           B           C           3

2           A           B           2
2           B           C           4
2           C           D           7

I'd like the get the difference in 'timer' between consecutive rows, but only those with the same issue_id.

Expected result:

issue_id:   state_one:  state_two:  timer:    time_diff:                 
1           B           C           3         2

2           B           C           4         2
2           C           D           7         3

When taking the time difference between two rows, I'd like the result displayed next to the later row.

If we only had one, time-ordered issue in the table, the following code works fine:

select 
    X.issue_id, 
    X.timer as X_timer,  
    Y.timer as Y_timer, 
    (X.timer - Y.timer) as time_diff
from trans X
cross join trans Y
where Y.timer in (
    select 
        max(Z.timer)
    from trans Z
    where Z.timer < X.timer);

I want to generalize this approach to handle MANY issues with time-ordered state changes.

My idea was to add the following condition, but it only works if consecutive events belong to the same issue (not the case in the real world):

... where Z.timer < X.timer)
and X.issue_id = Y.issueid;

Question: In MySQL, can I do this iteratively (i.e. calculate differences for issue_id=1, then for issue_id=2, and so on)? A function or subquery?

Other strategies? Constraint: I have read-only privileges. I truly appreciate the help!

EDIT: I added expected output, added a row to my example table, and clarified.

Upvotes: 1

Views: 381

Answers (3)

Strawberry
Strawberry

Reputation: 33945

Assuming timer or (issue_id,timer) is PRIMARY...

SELECT a.*, a.timer-MAX(b.timer)
  FROM trans a
  JOIN trans b
    ON b.issue_id = a.issue_id
   AND b.timer < a.timer
 GROUP
    BY a.issue_id
     , a.timer;

Upvotes: 1

Rohit Gupta
Rohit Gupta

Reputation: 455

Select * from #Temp

Select T1.Issuerid,T1.stateone,T1.statetwo,MAX(T1.timer)-MIN(T.timer) as  Time_Diff from #Temp T1
left join #Temp T2 on
T1.issuerid=T2.IssuerId
group by T1.Issuerid,T1.stateone,T1.statetwo

Please Give me Reply

Upvotes: 1

Sam
Sam

Reputation: 3155

select 
issue_id, (MAX(timer)-MIN(timer)) as diff from trans
group by issue_id

Upvotes: 3

Related Questions