...

View Full Version : Invalid use of group function



cyphix
07-05-2007, 11:52 PM
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!

mr_ego
07-05-2007, 11:58 PM
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

cyphix
07-06-2007, 12:55 AM
Thanks a bunch! That worked beautifully! :)

cyphix
07-06-2007, 01:08 AM
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!

mr_ego
07-08-2007, 11:16 AM
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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum