Re-using a calculated column

Hi,

I want to re-use a calculated column to make another calculated column. Like this:

SELECT (col1 + col2) AS calc1, (calc1*col3) AS calc2 FROM myfavouritetable WHERE this=working right


However, I'm getting the MySQL error Unknown column 'calc1' in 'field list'

I think this is possible in MySQL, but am I wrong?

show us what you actually want to do by providing example rows and output.

you can't use a derived column name in a WHERE clause, you can use it in a HAVING clause though.

Hi,

I want to use it in the field list, (before the FROM).

This is for a proximity search and I am trying to make a scaled value to order the results by.

Example rows of table:


Table: listings
idLIST | Name | lat | lon | rank
1 |John's Svc| -63 | 44 | 2
2 |Ed's Svc |-63.5 | 44 | 0


So then I'd like to execute this query:

SELECT Name, lat, lon,
(distance calculations) AS dist,
(CASE rank WHEN 3 THEN dist*0 WHEN 2 THEN dist*0.02 WHEN 1 THEN dist*0.04 ELSE dist END) AS rankDist
FROM listings
WHERE (same distance calculations) > 50


The real query is more involved, (the distance calculations are pretty crazy, and I don't always have lat & lon values so I subquery from a ZIP code table), but that's basically what's going on.

so change the WHERE to a HAVING

Hi,

The problem isn't caused by the WHERE calculation, it's caused by the SWITCH statement, (if I remove the SWITCH I have no problem, except I don't have the info I'm using the SWITCH to get).

It seems that MySQL doesn't like me using the dist column, (which is calculated), in the SWITCH statement to create a second calculated column, that's what I'm wondering about, sorry if I wasn't clear before.