Invalid use of group function

Can anyone tell me why this isn't working?


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!

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:

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.


SELECT stats_listing, count(stats_listing) AS stats_count
FROM listing_stat
GROUP BY stats_listing
ORDER BY stats_count DESC

Thanks a bunch! That worked beautifully! :)

Hmmmmm...... this is kinda odd; but any idea why the "stat_count" has doubled for the top two entries when using this modified 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!

Hmmmmm...... this is kinda odd; but any idea why the "stat_count" has doubled for the top two entries when using this modified 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):


HAVING stat_count > 0