mysqlgroup-bysum

SELECT query to group by a column and sum another column per group

I have two tables, I want to sum the value (personal_score) for ALL users on the same_team and temporarily assign it to the team_name, and then order them from highest to lowest, and finally only get the highest 10...

User
    - int id
    - varchar team_name
    - int personal_score

Team
   - int id
   - varchar team_name

So if that makes sense... the result would be something like...

   team_name   summed_score
   Chicago     92
   RedBulls    90
   Knights     82

Where summed_score is the SUM of ALL personal_score values in the User table that has a team_name equal to each team_name in the Team table.

Right now I do this like so using PHP, but I want to do it purely on the SQL level... Note I'm using medoo.php to query the database in php.

function ComparePersonalScores($a, $b)
{
    return $b["total_score"] - $a["total_score"];
}

// Get all the Team Names possible...
$Teams = $Database->select("teams", "team_name");
$TeamResult_Objects = [];
foreach($Teams as $team)
{
    $total_score = $Database->query("SELECT SUM(personal_score) as 'summed_score' FROM users WHERE team_name='" . $team . "';")->fetchAll();

    $result_object["total_score"] = $total_score[0]["summed_score"];
    $result_object["name"] = $team;
    array_push($TeamResult_Objects, $result_object);
}

usort($TeamResult_Objects, "ComparePersonalScores");
$TeamResult_Objects = array_slice($TeamResult_Objects, 0, 10);
Solution

  • You don't need to loop; just use SQL's built-in GROUP BY and aggregate functions.

    SELECT team_name,
       SUM(personal_score) AS summed_score
FROM User
GROUP BY team_name
ORDER BY summed_score DESC
LIMIT 10;