Jump to content

GROUP BY and ORDER BY help


TEENFRONT

Recommended Posts

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?

 

Link to comment
https://forums.phpfreaks.com/topic/216017-group-by-and-order-by-help/
Share on other sites

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?

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']

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.