2

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!

2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.