MySQL Query for Grouping and Summing Values Based on Specific Records

Use GROUP BY to group records, whereas SUM() function is used to add. Let us first create a table −

mysql> create table DemoTable
(
   Name varchar(40),
   Subject varchar(40),
   Marks int
);
Query OK, 0 rows affected (2.89 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('Chris','MySQL',76);
Query OK, 1 row affected (0.32 sec)
mysql> insert into DemoTable values('Sam','MongoDB',86);
Query OK, 1 row affected (0.39 sec)
mysql> insert into DemoTable values('Mike','MySQL',98);
Query OK, 1 row affected (0.46 sec)
mysql> insert into DemoTable values('David','Java',93);
Query OK, 1 row affected (0.32 sec)
mysql> insert into DemoTable values('Bob','MySQL',57);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('John','MongoDB',77);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-------+---------+-------+
| Name  | Subject | Marks |
+-------+---------+-------+
| Chris | MySQL   |    76 |
| Sam   | MongoDB |    86 |
| Mike  | MySQL   |    98 |
| David | Java    |    93 |
| Bob   | MySQL   |    57 |
| John  | MongoDB |    77 |
+-------+---------+-------+
6 rows in set (0.00 sec)

Following is the query to group and sum the values on the basis of SUBJECT −

mysql> select Subject,SUM(Marks) from DemoTable group by Subject;

This will produce the following output −

+---------+------------+
| Subject | SUM(Marks) |
+---------+------------+
| MySQL   |        231 |
| MongoDB |        163 |
| Java    |         93 |
+---------+------------+
3 rows in set (0.00 sec)