- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- 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 22:30:25
- Related Questions & Answers
- MySQL query to count frequency of students with the same age?
- Find out the records of students with more than a specific score in MySQL?
- Order by a single field and display rest of the records in the same order with MySQL
- Aggregation framework to get the name of students with test one score less than the total average of all the tests
- How to sort by value with MySQL ORDER BY?
- How to sum the values in the table by month with MySQL?
- MySQL Order by with case?
- MySQL - SUM rows with same ID?
- How can we create the MySQL view with ORDER BY clause?
- MySQL order by string with numbers?
- MySQL ORDER BY with EXPLAIN command
- MySQL ORDER BY with CASE WHEN
- MySQL ORDER BY strings with underscore?
- Aggregating rows in SAP ABAP with the same name
- How to restrict inserting an item with the same name in MongoDB?
Advertisements