group concat usage in MySQL

In this article, i am going to explaining usage of group_concat in MySQL with few example tables. Hope you people enjoy this concept, probably one who learn MySQL concepts. I am introduction two tables one is called "employee" table and "Department" table.

Structure and Data about employee table.

Id	name	Department_id
1	Madhan	1
2	nisha	2
3	janani	1
4	ajay	1
5	poovitha	2
6	ayyasamy	1
7	Jack	3

Structure and Data about department table.

Id	name
1	Accounts
2	IT
3	Product

Suppose if you want to query how many employees each and every department, you can use this following MySQL query.

SELECT department.name AS department_name,COUNT(*) AS department_total_employee FROM department
JOIN employee ON employee.department_id = department.id
GROUP BY employee.department_id

 Result of the above query

Department_name	Department_total_employee
Accounts	4
IT	2
Product	1

Just think about, now you got it how many employees available in each and every department.
If you want the detail, what the people belongs to which department in aggregate manner, you come know the real usage of GROUP_CONCAT method.

So What is GROUP_CONCAT?
" It is used to concatenate multiple column values into single string". If you won't use this, you have to lookup multiple rows and then concatenate them.

Note:
* In side group_concat method, you can override default seperator comma using the following command. Example group_concat(col.name SEPARATOR '--')

* You can change the order by also example

 group_concat(col.name order by col.name DESC)

The following query will give you the result of each and every department employee count as well as what are the employees on the particular department.

SELECT department.name AS department_name,COUNT(*) AS department_total_employee,GROUP_CONCAT(employee.emp_name) AS employees_under_department FROM department
JOIN employee ON employee.department_id = department.id
GROUP BY employee.department_id

  Result of the above query

Department_name	Department_total_employee	Employees_under_department
Accounts	4	Ayyasamy,janani,ajay,Madhan
IT	2	Poovitha,nisha
Product	1	jack