Combine unique values into a single row. in MySQL

Question

I have got this table named students that contains the department and the age of the students. Each row represents the student's department and their respective age.

department	age
Civil Engineering	19
Civil Engineering	20
Statistics	19
Statistics	19
Mechanical Engineering	21
Mechanical Engineering	20
Computer Science	25
Computer Science	24
Civil Engineering	21
Civil Engineering	22
Statistics	20
Mechanical Engineering	20

Now, I need to find the count of unique age in each department and also in the third column list all the unique ages corresponding to that department. The output should look like this:

department	count_unique _age	unique_ages
Civil Engineering	4	19,20,21,22
Statistics	2	19,20
Mechanical Engineering	2	20,21
Computer Science	2	24,25

Query I have tried -

SELECT DISTINCT department, COUNT(DISTINCT age) AS count_unique_age, 
FROM students
GROUP BY department

Using this query I am able to solve the first two columns. How do I solve for the third column?

Answer 1

That’s a simple aggregate query; in MySQL, we can use group_concat for string aggregation.

select department,
    count(distinct age) count_unique_age,
    group_concat(distinct age order by age separator ', ') distinct_ages
from mytable
group by department 

As we only count distinct values, I wonder whether it might be more efficient to deduplicate in a subquery first:

select department,
    count(age) count_unique_age,
    group_concat(age order by age separator ', ') distinct_ages
from (select distinct department, age from mytable) t
group by department