Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
576 Views
Advertisements
To Continue Learning Please Login