Find the ‘count of employees’ per department & name of those counted employees present in EMP table

Considering the already known EMP table, structure like:

TABLE EMP(EMPNO, ENAME, DEPTNO)

I want to fetch ‘count of employees’ department wise & name of employees present in that department using sql queries.

Able to fetch limited data,

select DEPTNO, COUNT(*) as 'Empcount' from EMP group by DEPTNO;

But this way I cant get the details of employees.

I am Expecting like:

DeptNo  Count       Ename
---------------------------------------
10       3          Ford,Allen,King
20       2          Raja,Miller
30       1          John

Thanks in advance for good answers and facts.

Answer

Like p.Salmon said in the comments use GROUP_CONCAT

SELECT 
    DEPTNO, COUNT(*) as 'Empcount', GROUP_CONCAT(DISTINCT ENAME) Ename  
FROM 
    EMP 
GROUP BY DEPTNO;

you can also add a ORDER BY ENAME DESC in thed GROUP_CONCAT if you want to sort the ENAME

Advertisement

Answer