Count with an order by

[EchoFool]

Hey,

 

If a database of users have a field named "ReferredBy" with the user id of the person that they were referred by... im trying to get a query that can then collect the top 20 users who have referred the most people.

 

So i tried Count(ReferredBy) As TotalReferred

 

But then realised without the WHERE clause stateing exactly which ID to count i can't make a simple list like:

 

User1    Referred 10

User4    Referred 7

User2    Referred 3

 

(DESC order basically).

 

How would i make a query to do that with a count and without actually referencing a precise ID?

[fenway]

why not simply group by referred_by, and order by the count desc, limit 20?

[EchoFool]

So like :

SELECT Count(ReferredBy) AS Total FROM tablename
GROUP BY Count(ReferredBy) ORDER BY Total DESC

 

Or is there a better way?

[fenway]

Pretty close.. of course, you'll want back to ReferredBy column itself, and LIMIT 10.

[EchoFool]

What you mean by want back to ReferredBy column itself?

[fenway]

Well, how else are you going to identify which users we're talking about?

[EchoFool]

Oh i see - of course

 

You mean this:

SELECT Count(ReferredBy) AS Total,ReferredBy FROM tablename
GROUP BY Count(ReferredBy)  ORDER BY Total DESC