Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re-using a calculated column

    Hi,

    I want to re-use a calculated column to make another calculated column. Like this:
    Code:
    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?
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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:

    Code:
    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:
    Code:
    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.
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    so change the WHERE to a HAVING

  • #5
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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.
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •