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 2 of 2
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Unhappy mysql unknown column count1? can someone help please

    Hi All,

    I have the following query
    Code:
    SELECT *,
    SUM(CASE WHEN title LIKE '%fish%' THEN 1 ELSE 0 END) AS count1, 
    SUM(CASE WHEN title LIKE '%soup%' THEN 1 ELSE 0 END) AS count2,
    SUM(count1+count2)AS matches 
    FROM tbl_recipes 
    GROUP BY recipeID
    Which checks the value of the recipe title to see if it contains the keyword anywhere in it, and set a 1 if found and 0 if not, this bit is working well BUT i can't for the love of me figure out how i can then get the total sum of count1 and count2, its keeps coming up as invalid column count1 in query

    Its been driving me mad for hours and i just cant seem to solve this

    Would really appreciate any help with this
    Thanks

    Luke
    Last edited by LJackson; 06-11-2014 at 10:13 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    you can't refer to a created column in the SELECT as it doesn't exist at that point. (You can refer to column1 + column2 in the HAVING clause or the ORDER BY clause for instance).
    So you'd have to use
    Code:
    SUM(CASE WHEN title LIKE '%fish%' OR title LIKE '%soup%' THEN 1 ELSE 0 END) AS matches
    in the select clause.


  •  

    Posting Permissions

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