Combine Group_Concat() with counts

from the Artful Common Queries page


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) |

+------------------------+




Return to the Artful Common Queries page