How to sum the score of students with the same name in MySQL with ORDER BY?

For this, use ORDER BY along with GROUP BY clause. Let us first create a table with Student Name and Score −

mysql> create table countRowValueDemo    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20),    -> StudentMathScore int    -> ); Query OK, 0 rows affected (0.71 sec)

Following is the query to insert records in the table using insert command −

mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('Larry',45); Query OK, 1 row affected (0.19 sec) mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('Mike',56); Query OK, 1 row affected (0.16 sec) mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('John',60); Query OK, 1 row affected (0.15 sec) mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('David',40); Query OK, 1 row affected (0.24 sec) mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('David',70); Query OK, 1 row affected (0.12 sec) mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('John',80); Query OK, 1 row affected (0.13 sec) mysql> insert into countRowValueDemo(StudentName,StudentMathScore) values('David',88); Query OK, 1 row affected (0.17 sec)

Following is the query to display all records from the table using select statement −

mysql> select * from countRowValueDemo;

This will produce the following output −

+-----------+-------------+------------------+ | StudentId | StudentName | StudentMathScore | +-----------+-------------+------------------+ | 1         | Larry       | 45               | | 2         | Mike        | 56               | | 3         | John        | 60               | | 4         | David       | 40               | | 5         | David       | 70               | | 6         | John        | 80               | | 7         | David       | 88               | +-----------+-------------+------------------+ 7 rows in set (0.00 sec)

Case 1: Descending order (Sum)

Following is the query to sum the score of students with similar name. The result will be displayed in descending order −

mysql> select StudentName,    -> sum(StudentMathScore) AS TOTAL_SCORE    -> from countRowValueDemo    -> group by StudentName    -> order by sum(StudentMathScore) desc;

This will produce the following output −

+-------------+-------------+ | StudentName | TOTAL_SCORE | +-------------+-------------+ | David       | 198         | | John        | 140         | | Mike        | 56 | | Larry       | 45 | +-------------+-------------+ 4 rows in set (0.00 sec)

Case 2: Ascending order (Sum)

Following is the query to sum the score of students with similar names. The result will be displayed in descending order −

mysql> select StudentName,    -> sum(StudentMathScore) AS TOTAL_SCORE    -> from countRowValueDemo    -> group by StudentName    -> order by sum(StudentMathScore);

This will produce the following output −

+-------------+-------------+ | StudentName | TOTAL_SCORE | +-------------+-------------+ | Larry       | 45 | | Mike        | 56 | | John        | 140         | | David       | 198         | +-------------+-------------+ 4 rows in set (0.00 sec)