MySQL reference to avoid repetition?

Question

I have this snippet:

SELECT CASE WHEN AVG(UNIX_TIMESTAMP(tDone)-UNIX_TIMESTAMP(tIPN))/3600 >= 10 THEN ROUND(AVG(UNIX_TIMESTAMP(tDone)-UNIX_TIMESTAMP(tIPN))/3600,0) ELSE ROUND(AVG(UNIX_TIMESTAMP(tDone)-UNIX_TIMESTAMP(tIPN))/3600,1) END FROM ... 

Can I do anything to remove duplication? Something like these lines, for example: (The following is a hypothetical code):

 SET var = AVG(UNIX_TIMESTAMP(tDone)-UNIX_TIMESTAMP(tIPN))/3600 SELECT CASE WHEN var > 10 THEN ROUND(var,0) ELSE ROUND(var,1) END FROM ... 

Answer 1

In the subquery, you can do something like this:

 SELECT CASE WHEN avgtiPN >= 10 THEN ROUND(avgtiPN,0) ELSE ROUND(avgtiPN,1) END FROM (SELECT AVG(UNIX_TIMESTAMP(tDone)-UNIX_TIMESTAMP(tIPN))/3600 AS avgtiPN FROM ...) AS AVGQuery 

But I'm still not sure if this is more readable.

Answer 2

Yes you can, but the processing order of the variable is undefined for user-defined variables. This link in the MySQL documentation explains when it works and when it does not work.