Can we use MySQL GROUP BY clause with multiple columns like MySQL DISTINCT clause is used?

MySQLMySQLi Database

Yes, it is possible to use MySQL GROUP BY clause with multiple columns just as we can use MySQL DISTINCT clause. Consider the following example in which we have used DISTINCT clause in first query and GROUP BY clause in the second query, on ‘fname’ and ‘Lname’ columns of the table named ‘testing’.

mysql> Select * from testing;
+------+---------+---------+
| id   | fname   | Lname   |
+------+---------+---------+
|  200 | Raman   | Kumar   |
|  201 | Sahil   | Bhalla  |
|  202 | Gaurav  | NULL    |
|  203 | Aarav   | NULL    |
|  204 | Harshit | Khurana |
|  205 | Rahul   | NULL    |
|  206 | Piyush  | Kohli   |
|  207 | Lovkesh | NULL    |
|  208 | Gaurav  | Kumar   |
|  209 | Raman   | Kumar   |
+------+---------+---------+
10 rows in set (0.00 sec)

mysql> Select DISTINCT FNAME,LNAME from testing;
+---------+---------+
| FNAME   | LNAME   |
+---------+---------+
| Raman   | Kumar   |
| Sahil   | Bhalla  |
| Gaurav  | NULL    |
| Aarav   | NULL    |
| Harshit | Khurana |
| Rahul   | NULL    |
| Piyush  | Kohli   |
| Lovkesh | NULL    |
| Gaurav  | Kumar   |
+---------+---------+
9 rows in set (0.00 sec)

mysql> Select Fname, LNAME from testing GROUP BY Fname,Lname;
+---------+---------+
| Fname   | LNAME   |
+---------+---------+
| Aarav   | NULL    |
| Gaurav  | NULL    |
| Gaurav  | Kumar   |
| Harshit | Khurana |
| Lovkesh | NULL    |
| Piyush  | Kohli   |
| Rahul   | NULL    |
| Raman   | Kumar   |
| Sahil   | Bhalla |
+---------+---------+
9 rows in set (0.00 sec)

The only difference is that the result set returns by MySQL query using GROUP BY clause is sorted and in contrast, the result set return by MySQL query using DISTICT clause is not sorted.

raja
Published on 22-Feb-2018 07:24:48
Advertisements