GROUP BY and ORDER BY help

[TEENFRONT]

Hey

 

ok, i have a mysql table that show which user supports which team. example table results: uid is userid and team is team name.

 

uid  team

3      leeds

6      wigan

1      leeds

9      warrington

2      wigan

 

What i want to output is a "league table" showing how many supporters each team has.. the result needs to be this

 

1. Leeds (3fans)

2. Wigan (2fans)

3. Warrington (1fan)

 

This would be

 

$position. $team ($teamFans )

 

 

So the query needs to group by team name, then count the number of fans, then output the team name and number of fans.

 

I can do the GROUP BY bit... and output all the team names a single time... but just lost on how im meant to count up the fans and pass $teamFans to the result..

 

Any help?

 

[PFMaBiSmAd]

See the COUNT() function at this link - http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

[TEENFRONT]

Thanks iv got this so far...

 

SELECT team FROM `userfields` GROUP BY team ORDER BY COUNT(team) DESC

 

This successfully orders by the teams with the most fans... and i can output the team name... so i have this

 

1. Leeds

2. Wigan

3. Warrington

 

But how to i add the actual number of fans for each team?

[PFMaBiSmAd]

To get the count returned, you need to put it into the SELECT list, Since you are also using it in the order by, use an alias so that you don't need to repeat it and since you are counting everything in each group, use COUNT(*) -

 

SELECT team, COUNT(*) as cnt FROM `userfields` GROUP BY team ORDER BY cnt DESC

 

You can access the cnt value in your php code using $row['cnt']

[TEENFRONT]

Thats fantastic! You have helped me out quite a few times... il buy you a coffee (paypal) if you pm me cheers!