Rather than list instances including repeats on one line, you want to list distinct instances and their counts. One way is to do a simple GROUP BY query and in your application layer remove the newlines from the result. Or you can do it in one step.
-- ---------------------------------------------------------------------------
-- Combine Group_Concat() with counts
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/queries.php
-- ---------------------------------------------------------------------------
-- Rather than list instances including repeats on one line, you want to list
-- distinct instances and their counts. One way is to do a simple GROUP BY query
-- and in your application layer remove the newlines from the result. Or you
-- can do it in one step:
drop table if exists t;
create table t (
type int(10) ,
instance int(10)
);
insert into t values
(1,4),(1,7),(1,9),(1,10),(2,2),(2,3),(2,5),(2,6),(2,8),(3,1),(4,11);
select group_concat( concat( type,'(',qty,')') separator ', ') list
from (
select type, count(*) qty
from t
group by type
) n
-- +------------------------+
-- | list |
-- +------------------------+
-- | 1(4), 2(5), 3(1), 4(1) |
-- +------------------------+