How to perform conditional GROUP BY in MySQL to fetch?

Let us first create a table −

mysql> create table DemoTable
(
   StudentName varchar(40),
   StudentMarks int
);
Query OK, 0 rows affected (0.64 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('John',78);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Chris',48);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('John',67);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('Chris',89);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output. Here, we have some duplicate student names like the student “John” with marks 78 and 67 −

+-------------+--------------+
| StudentName | StudentMarks |
+-------------+--------------+
| John        |           78 |
| Chris       |           48 |
| John        |           67 |
| Chris       |           89 |
+-------------+--------------+
4 rows in set (0.00 sec)

Following is the query to perform conditional GROUP BY and display distinct Student Name with maximum marks. For example, student “John” with maximum marks 78 −

mysql> select StudentName,Max(StudentMarks) AS StudentMarks from DemoTable group by StudentName;

This will produce the following output −

+-------------+--------------+
| StudentName | StudentMarks |
+-------------+--------------+
| John        |           78 |
| Chris       |           89 |
+-------------+--------------+
2 rows in set (0.00 sec)

AmitDiwan

Updated on: 01-Oct-2019

219 Views

Kickstart Your Career

Get certified by completing the course

Get Started