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?

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 …

All 3 Replies

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

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

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
{