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)

Anvi Jain

Published on 15-Apr-2019 13:59:08

Previous Page Print Page