Sorting By COUNT

cap97

Maybe this is more of a MySQL dilema, but here goes. I have a banners table and I want to get a count of how many banners people made. So I go:


SELECT Credit, COUNT(BannerID) FROM camb_banners GROUP BY Credit;

And end up with:


DragynWulf: 15 banners
Texcap: 5 banners
Lightning Strike: 11 banners
Michael Kaiser: 3 banners

That's all great except I'd like the order to go from greatest to least amount of banners, instead of alphabetically by credit. However, I have yet to figure out how to sort a MySQL query by its COUNTs. Any ideas? One person once suggested creating a temporary table, which worked in MySQL, but I couldn't get it to fly in PHP.

kriek

Originally posted by cap97
I have yet to figure out how to sort a MySQL query by its COUNTs.

ORDER BY COUNT and/or DESC

Providing that COUNT is a valid field of course. 😉

cap97

Nope! "ORDER BY COUNT" gives me an "unknown column" message and "ORDER BY COUNT(BannerID) gives me "illegal use of GROUP function" message. Any other ideas?

xblue

SELECT Credit, COUNT(BannerID) AS BannerCount FROM camb_banners GROUP BY Credit ORDER BY BannerCount DESC

This should work. Make sure you use ORDER BY after GROUP BY and ORDER BY column name or alias.