Re: referencing column aliases in select list

Seb wrote:
> ---<---------------cut here---------------start-------------->---
> SELECT table1.col1 - table2.col1 AS diff1,
> table1.col2 + table2.col2 AS sum1,
> sum1 - diff1
> FROM table1 INNER JOIN table2 ON (table1.id = table2.id)
> ---<---------------cut here---------------end---------------->---
>
> but this fails with the error message that sum1 column is not known.
> How can one get around this? The calculations are quite involved and
> would be difficult and error-prone to repeat them anywhere they're
> needed in the select list. Thanks in advance for any pointers.

The way I usually handle this is as follows.

SELECT foo - bar AS baz FROM (
SELECT a.a + b.a AS foo, a.b + b.b AS bar FROM a JOIN b ON a.id = b.id
) AS subtable;

Although, I'm not really sure that's The Right Way to do it.

Colin