[SOLVED] Counting actual rows in addition to GROUP BY?

[Edward]

 

Hi,

 

I have just added a rating system to one of my client's sites, so people can rate the artist's CDs from 1 to 5. Here is an example table:

 

cd_name score

CD1 5

CD2 4

CD1 3

CD2 4

CD3 2

CD4 4

 

On the Home page I want to show some information on the highest rated CD, so I am using this code:

 

<?php

$mysql_connection_errors = connect_to_mysql();

if (empty($mysql_connection_errors)) {

$sql = 'SELECT cd_name, SUM(rating) FROM my_table GROUP BY cd_name ORDER BY SUM(rating) DESC LIMIT 1;';

$result = mysql_query($sql);

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

$cd_name = $row['cd_name'];

$rating = $row['SUM(rating)'];

}

mysql_close();

}

?>

 

However, I need to prepare for the fact that there may be two or more CDs with the same score, so does anyone know how to also order my the number of rows returned? As far as I am aware, using GROUP BY will always return one, and won't count the number of rows it's grouping? Once I can do this, I will also order by cd_release_date so that if any have the same highest score, AND the same amount of votes (as per example table above), it will display the newest CD.

 

This has me stuck, any help wold be grrrrrrrreat!

 

Thanks,

 

Ed

[Snart]

What if you add COUNT(cd_name) to the SELECT part?

[Edward]

Geeeeenius! Great, I'l try, thank you! That's new to me, I only just discovered SUM. Thanks again.