How to Order Rows by Group Sum in SQL

Problem:

You’d like to order rows by the sums generated by a group of records.

Example:

Our database has a table named training with data in four columns: id, login, year, and score.

Let’s get the login name of each player along with the total sum of score across all years, putting records in descending order according to players’ total scores.

Solution:

We’ll use the operator ORDER BY to order records based on the aggregate function SUM(), which calculates the total score for each player across all years.

Here’s the query you’d write:

SELECT login,
  SUM(score) AS total_score
FROM training
GROUP BY login
ORDER BY SUM(score) DESC;

Here’s the result:

Discussion:

Use ORDER BY if you want to order rows according to a value returned by an aggregate function like SUM(). The ORDER BY operator is followed by the aggregate function (in our example, SUM()). DESC is placed after this function to specify a descending sort order. Thus, the highest aggregate values are displayed first, then progressively lower values are displayed. To sort in ascending order, you can specify ASC or simply omit either keyword, as ascending is the default sort order.

In the query above, we select each player’s login and the sum of their score for all years. This total score is calculated using SUM() with the score column as an argument. We add an alias for this aggregate value (SUM(score) AS total_score); you can use this alias instead of the aggregate function in the ORDER BY clause (ORDER BY total_score DESC).

Notice that we include login in the GROUP BY. If we include a column in SELECT, we must also use this column in GROUP BY. In this example, we use GROUP BY clause followed by the column login because we put this column in the SELECT. Notice that GROUP BY is placed before ORDER BY in the query.