On gists

Group Concat with count

MySql MySQL - ArtfulSoftware

q.sql Raw #

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 ', ') 
  as list 
from (
  select type, count(*) qty 
  from t 
  group by type
) n
+------------------------+
| list                   |
+------------------------+
| 1(4), 2(5), 3(1), 4(1) |
+------------------------+