Alias the long repeated expression in postgresql query

Question

Is there any way in postgresql to not repeat myself when the same expression is used in select and where blocks?

select (
    |/(( power((consumption_2011 - consumption_3y/3), 2)
       + power((consumption_2012 - consumption_3y/3), 2)
       + power((consumption_2013 - consumption_3y/3), 2)
    ) / 3)) as deviation
from consumption
where (
    |/(( power((consumption_2011 - consumption_3y/3), 2)
       + power((consumption_2012 - consumption_3y/3), 2)
       + power((consumption_2013 - consumption_3y/3), 2)
    ) / 3) > 0.8
)

Ihor Romanchenko · Accepted Answer · 2014-01-30 14:17:56Z

3

You can do something like that:

select deviation 
from (
select 
    (( power((consumption_2011 - consumption_3y/3), 2)
      + power((consumption_2012 - consumption_3y/3), 2)
      + power((consumption_2013 - consumption_3y/3), 2)
    ) / 3) as deviation
from consumption) sub
where deviation > 0.8

answered Jan 30, 2014 at 14:17

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Alias the long repeated expression in postgresql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related