Counting rows

[[vengeance]]

Hello.

I have a field called 'badges' in my database with the columns:

| id | badge | userid |

I'm creating a statistics page which lists the top 5 users with the most badges. However, I cannot simply do ORDER BY `badges` DESC, because I didn't set it up that way.

How can I group all badges with the same userid together, and count how many there are, and then order them in descending order on my site?

[ckeyrouz]

Try this:
select * from (
select count(badges) myCount, id
from yourtable
group by id) subTable
order by myCount desc
limit 5

Note that you should replace the word yourtable with the real name of your table.

[[vengeance]]

Try this:
select * from (
select count(badges) myCount, id
from yourtable
group by id) subTable
order by myCount desc
limit 5

Note that you should replace the word yourtable with the real name of your table.
Thanks, but that doesn't quite work for me.

I did replace yourtable with the actual name, which is badges. However, when I echo'd out $badges['userid'] in PHP it returned nothing, which it should have, because the badges table holds it.

Or am I misunderstanding your code here? What I want to is to display the userid and how many badges were found on that user, and listing them in a top 5 order.

[Fumigator]

You need to be more clear. You say you have a field called 'badges'...??? Did you mean to say you have a table named 'badges'?

At any rate, GROUP BY is what you use, but you need to group by userid, not id.

[[vengeance]]

You need to be more clear. You say you have a field called 'badges'...??? Did you mean to say you have a table named 'badges'?

At any rate, GROUP BY is what you use, but you need to group by userid, not id.
Ah, yeah. I meant my table name is called 'badges'. Sorry about that.
And I'm a newbie when it comes to SQL queries, etc.

Could you give me an example of how to use GROUP BY?

[Fumigator]

ckeyrouz gave you one :p

[ckeyrouz]

this is the mofidied version:

select * from (
select count(badge) myCount, userid
from badges
group by userid) subTable
order by myCount desc
limit 5

[Old Pedant]

I don't get it, why do you need the inner query???

How does that give a different answer than simply doing:

select userid, Count(badge) AS myCount
from badges
group by userid
order by myCount desc
limit 5

If MySQL doesn't let you use myCount in the ORDER BY because it's a derived field, just use:
select userid, Count(badge) AS myCount
from badges
group by userid
order by Count(badge) desc
limit 5

And don't worry, the SQL engine is smart enough not to need to do the COUNT( ) twice.

Finally, there's no real reason to use COUNT(badge). This will give the same answer:

select userid, Count(*) AS myCount
from badges
group by userid
order by Count(*) desc
limit 5

Unless, of course, there are some records where the field badge is null.

[[vengeance]]

Finally, there's no real reason to use COUNT(badge). This will give the same answer:

select userid, Count(*) AS myCount
from badges
group by userid
order by Count(*) desc
limit 5

Unless, of course, there are some records where the field badge is null.

Thanks, that worked for me!