Can we GROUP BY one column and select all data in MySQL?

MySQLMySQLi Database

Yes, you can use group_concat() for this. Let us first create a table −

mysql> create table groupByOneSelectAll
   -> (
   -> StudentDetails varchar(100),
   -> StudentName varchar(100)
   -> );
Query OK, 0 rows affected (0.91 sec)

Following is the query to insert some records in the table using insert command −

mysql> insert into groupByOneSelectAll values('StudentFirstName','John');
Query OK, 1 row affected (0.14 sec)

mysql> insert into groupByOneSelectAll values('StudentFirstName','Chris');
Query OK, 1 row affected (0.21 sec)

mysql> insert into groupByOneSelectAll values('StudentFirstName','Robert');
Query OK, 1 row affected (0.65 sec)

mysql> insert into groupByOneSelectAll values('StudentFirstName','Bob');
Query OK, 1 row affected (0.13 sec)

mysql> insert into groupByOneSelectAll values('StudentFirstName','David');
Query OK, 1 row affected (0.15 sec)

Following is the query to display all records from the table using select statement −

mysql> select * from groupByOneSelectAll;

This will produce the following output −

+------------------+-------------+
| StudentDetails   | StudentName |
+------------------+-------------+
| StudentFirstName | John        |
| StudentFirstName | Chris       |
| StudentFirstName | Robert      |
| StudentFirstName | Bob         |
| StudentFirstName | David       |
+------------------+-------------+
5 rows in set (0.00 sec)

Following is the query to group by a single column −

mysql> select StudentDetails,group_concat(StudentName) from groupByOneSelectAll group
by StudentDetails;

This will produce the following output −

+------------------+-----------------------------+
| StudentDetails   | group_concat(StudentName)   |
+------------------+-----------------------------+
| StudentFirstName | John,Chris,Robert,Bob,David |
+------------------+-----------------------------+
1 row in set (0.05 sec)
raja
Published on 16-Apr-2019 17:14:07
Advertisements