Count number of names grouped

[digi duck]

Hi. I have the following code where I grab the names and average overall rating from my database and group by the name.

 

$sql="SELECT name, AVG(overall) AS overall FROM $tbl_name GROUP BY name ORDER BY AVG(overall) DESC";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){

 

Does anybody know how I can count the number of names that have been grouped together?

 

For instance:

 

Name    Overal ratingl

John      2

Pete      5

John      8

 

It should group the john's, calculate the average rating (8+2)/2=5, and then show that there have been 2 johns making up the 5 rating?

 

I've tried using the code below and messed around with it loads but cant get it to work:

 

$sql="SELECT name, AVG(overall) AS overall FROM $tbl_name COUNT name GROUP BY name ORDER BY AVG(overall) DESC";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){

echo $rows['Count name']; 

 

Thanks in advance.

[digibucc]

i can think of manually running a loop and setting a counter +1 for every time it finds that name in the array, but that is ugly and won't scale. i don't recommend it.

i am eager to see the best response for this as I am sure there is a simpler bit of code than a comparison loop.

[PaulRyan]

You weren't too far off Digi Duck, you just had your logic wrong

 

This is a tested and working example, try it out:

<?PHP

  $query = "SELECT `name`, AVG(`overall`) AS `average_rating` FROM {$tbl_name} GROUP BY `name` ORDER BY `overall` DESC";
  
  $doQuery = mysql_query($query);
  
  while($result = mysql_fetch_assoc($doQuery)) {
    echo '<pre>';
    print_r($result);
    echo '</pre>';
  }

?>

 

Regards, PaulRyan.

[DavidAM]

The COUNT function should be part of your SELECT list:

 

SELECT name, AVG(overall) AS overall, COUNT(*) AS counted
FROM $tbl_name 
GROUP BY name 
ORDER BY AVG(overall) DESC";

 

 

[digi duck]

Thanks so much everybody for your help it worked!!