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

    Rewrite this query legally

    I want to do this:
    Code:
    SELECT COM_idLST, COUNT(idCOMMENT) AS Comments 
    FROM COMMENT 
    GROUP BY idCOMMENT 
    WHERE Comments > 5
    But as we know I can't use aliases in the WHERE clause. I tried putting the COUNT() function there as well but that didn't work. How can I write a query that will do what I want it to, (I think the desired result is apparent from the bad code above), but is actually possible?

    Thanks in advance,
    John
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Try this (not sure if it works in mySQL)

    HAVING Comments > 5
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #3
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    I was able to run the query successfully. I'll do some testing and see if it returns the results I expect.
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #4
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    That was exactly right. Thank you very much.
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #5
    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
    Note a WHERE clause must come before a GROUP BY clause and a HAVING clause comes after the GROUP BY clause. A HAVING clause should only be used when you are using a GROUP BY clause as it pertains to the GROUP BY and not individual rows.

  • #6
    Regular Coder
    Join Date
    Apr 2003
    Location
    Montreal, QC
    Posts
    340
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Yeah, I ditched the WHERE entirely and used HAVING to create a condition.
    Search for Laughter or Just Search?
    GiggleSearch.org
    Blog: www.johnbeales.com
    All About Ballet: www.the-ballet.com

  • #7
    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
    Sure, I figured you had done that. Was just clarifying the point for those not familiar with using a HAVING 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
    •