Artful Common Queries

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

Last updated 27 Jan 2020