User variables - fighting syntax

Question

Can anyone set my MySQL syntax straight please? I am trying to set a user variable called "seconds" to equal the output of the query shown below (and it does work by it's self), but I am constantly geting "You have an error in your SQL syntax" errors.

SET @seconds=AVG(t2.epoch-t1.epoch)
             FROM tmp_4045_metrics AS t1, tmp_4045_metrics AS t2
             WHERE t1.seq+1 = t2.seq

I have tried SELECT AVG...., (SELECT AVG...), and quite a few others, but always the same error warning that my syntax is incorrect and never any clues to where it's incorrect.

Can anyone point me in the right direction?

Thanks, Rich

Answer 1

If i remember correctly (and it works on MS SQL Server at work), this should do it:

Select @seconds=AVG(t2.epoch-t1.epoch)
             FROM tmp_4045_metrics AS t1, tmp_4045_metrics AS t2
             WHERE t1.seq+1 = t2.seq

Answer 2

Now the SELECT @seconds=... works error free - cracking :) and SELECT @seconds returns, but it's empty.

And I have triple checked the statement returns a value (300 seconds to be precise). Back to the drawing board

Thanks for the suggestion though, better than I had achieved by myself.