Group the marks of a particular student from a table and display total marks in a separate column for each student?

MySQLMySQLi Database

To group marks, use MySQL GROUP BY. To sum, use MySQL sum()function. Let us first create a table −

mysql> create table DemoTable1920
   (
   StudentName varchar(20),
   StudentMarks int
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1920 values('Chris',67);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1920 values('David',97);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1920 values('Chris',57);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1920 values('David',45);
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1920 values('Chris',89);
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1920;

This will produce the following output −

+-------------+--------------+
| StudentName | StudentMarks |
+-------------+--------------+
| Chris       |           67 |
| David       |           97 |
| Chris       |           57 |
| David       |           45 |
| Chris       |           89 |
+-------------+--------------+
5 rows in set (0.00 sec)

Here is the query to group marks of a particular student −

mysql> select StudentName,SUM(StudentMarks) as TotalMarks from DemoTable1920
    group by StudentName;

This will produce the following output −

+-------------+------------+
| StudentName | TotalMarks |
+-------------+------------+
| Chris       |        213 |
| David       |        142 |
+-------------+------------+
2 rows in set (0.00 sec)
raja
Published on 30-Dec-2019 07:10:56
Advertisements