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 2004
    Posts
    684
    Thanks
    24
    Thanked 1 Time in 1 Post

    Invalid use of group function

    Can anyone tell me why this isn't working?

    Code:
    SELECT DISTINCT (stat_listing)
    FROM listing_stat
    ORDER BY COUNT(stat_listing) DESC;
    I have a table with the same values in one table (stat_listing) & I want to return distinct tables sorted by the value that appears the most - I hope you understand what I mean.

    Thanks!

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Location
    Brisbane, Australia
    Posts
    181
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Code:
    SELECT DISTINCT (stat_listing)
    FROM listing_stat
    ORDER BY COUNT(stat_listing) DESC;
    Firstly, DISTINCT is a Keyword, not a function, so you dont need the parenthesis.

    Example:
    Code:
    SELECT DISTINCT User
    What you probably want to do is something like this (i haven't tested this and i dont know if it works)

    You dont need the distinct in this statement because the GROUP BY clause does that for you.

    Code:
    SELECT stats_listing, count(stats_listing) AS stats_count
    FROM listing_stat
    GROUP BY stats_listing
    ORDER BY stats_count DESC
    -mR_eGo
    _______________________
    Programming since
    3 years old.

  • #3
    Regular Coder
    Join Date
    Apr 2004
    Posts
    684
    Thanks
    24
    Thanked 1 Time in 1 Post
    Thanks a bunch! That worked beautifully!

  • #4
    Regular Coder
    Join Date
    Apr 2004
    Posts
    684
    Thanks
    24
    Thanked 1 Time in 1 Post
    Hmmmmm...... this is kinda odd; but any idea why the "stat_count" has doubled for the top two entries when using this modified code?

    Code:
    SELECT stat_listing, count(stat_listing) AS stat_count
    FROM listing_stat,listing_photo
    WHERE photo_listing=stat_listing AND
    stat_listing != 0
    GROUP BY stat_listing
    ORDER BY stat_count DESC;
    "photo_listing" is a table in the "listing_photo" table; what I'm trying to do there is make sure that the entry exists in that table for it to "qualify" to show as a result.

    Thanks again!

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    Brisbane, Australia
    Posts
    181
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by cyphix View Post
    Hmmmmm...... this is kinda odd; but any idea why the "stat_count" has doubled for the top two entries when using this modified code?

    Code:
    SELECT stat_listing, count(stat_listing) AS stat_count
    FROM listing_stat,listing_photo
    WHERE photo_listing=stat_listing AND
    stat_listing != 0
    GROUP BY stat_listing
    ORDER BY stat_count DESC;
    "photo_listing" is a table in the "listing_photo" table; what I'm trying to do there is make sure that the entry exists in that table for it to "qualify" to show as a result.

    Thanks again!
    I think what you want is more like this (added to the code i showed you):

    Code:
    HAVING stat_count > 0
    -mR_eGo
    _______________________
    Programming since
    3 years old.


  •  

    Posting Permissions

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