COUNT(), GROUP BY and GROUP_CONCAT() together on MySQL

Question

My question is quite simple. I just want to count events from each client and present them in a comma separated single row.

I have this table:

+----+----------------+
| id | event          |
+----+----------------+
| 22 | a              |
| 23 | bb             |
| 24 | bb             |
| 25 | ccc            |
| 26 | ccc            |
| 27 | ccc            |
+----+----------------+
6 rows in set (0.01 sec)

So far I have this following query:

SELECT COUNT(event) AS total FROM test_table GROUP BY event;

Which gives me the following result:

+--------------+
| count(event) |
+--------------+
|            1 |
|            2 |
|            3 |
+--------------+

I'm trying to use GROUP_CONCAT() in order to show them in a single line, like this, but I tried all different approaches and I didn't get the desired result:

+--------------+
| result       |
+--------------+
| 1, 2, 3      |
+--------------+

Do you have a clue on how to do that?

Thanks in advance!

Answer 1

To create the result you wanted, I used a subquery and used GROUP_CONCAT() from there.

create table test
(
  id int,
  event varchar(3)
  );
  insert into test
  values
  (22,'a'),(23,'bb'),(24,'bb'),(25,'ccc'),(26,'ccc'),(27,'ccc')


select group_concat(total)
from
(
SELECT COUNT(event) AS total FROM test GROUP BY event
  )a

+---------------------+
|group_concat(total)  |
+---------------------+
|1,2,3                |
+---------------------+

DB Fiddle

Answer 2

You could group by event in this way:

select event, 
       group_concat(id, ',') ids, 
       count(*) cnt
from   events
group by event;

select event, group_concat(id, ',') ids, count(*) cnt
from   events
group by event

event | ids         | cnt
:---- | :---------- | --:
a     | 22,         |   1
bb    | 23,,24,     |   2
ccc   | 25,,26,,27, |   3

db<>fiddle here