Newbie: Count and select multiple rows and show them as a list

[grantcharov]

Okay, I am a total newbie stuck with the following task.

I have one table, that looks like this:

| id | name | rating | date |

What I am trying to do is gettin an output showing which 'name' has the best 'rating' based on months,
showing a top 5 of those users. The value in the 'rating' row is from 1-5.

So first I have to count the sum of the 'rating' for each 'name', and then I have to put those names in a list,
ordered by the one with the biggest value.

Output example:

1 : user A , 55 points

2 : user B , 45 points

etc

How could I achive this?

[smantscheff]

Learn about aggregate functions like AVG and SUM and about the GROUP BY clause in SELECTs.

[tpunt]

It's as simple as querying your database for the three columns (id, name, rating) with the total sum of the "rating" column being ordered to descending. Using the GROUP BY clause to put all of the same names together, we then use the SUM() (aggregate) function in mysql to count the total of the users rating. The LIMIT clause is then used to only grab a maximum of 5 tuples from our table. After our query, we use the data collected to loop through the result set. Becuase you did not state what database API you're looking to work with, I'll work with the procedural mysqli_ extension for the following code example:

<?php

$con = mysqli_connect('host','username', 'password') or die(mysqli_error($con));
mysqli_select_db($con, 'database') or die(mysqli_error($con));

#query database
$query = mysqli_query($con, "SELECT id,name,SUM(rating) FROM table GROUP BY name ORDER BY SUM(rating) DESC LIMIT 5");

#check for a result set
if($query && mysqli_num_rows($query) > 0)
{
    while($res = mysqli_fetch_assoc($query)) #loop through result set
    {
        echo "{$res['id']}: {$res['name']}, {$res['SUM(rating)']} points<br />";
    }
}
else #if no result set, then output a message
{
    echo 'No results.';
}

?>

I'd also suggest to change your table name from "date" to something else because it's a reserved word in MySQL; even if you're using grave accents to encase the table name (which in itself promotes the use of poor naming conventions).

[grantcharov]

Thanks a lot for your solution, SagaciousDev ! :)