Codemonkey
Codemonkey

Reputation: 4807

MySQL referencing, to avoid repeating myself?

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 the duplication from this? Something along these lines, for instance: (Hypothetical code follows):

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
    ...

Upvotes: 5

Views: 61

Answers (2)

ForguesR
ForguesR

Reputation: 3618

With a 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 am still uncertain if it is more readable.

Upvotes: 2

tony gil
tony gil

Reputation: 9554

Yes, you can, but variable processing order is undefined for user-defined variables. This reference in the MySQL documentation explains when this works and when it doesnt.

Upvotes: 1

Related Questions