3

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!

Improve this question

2 Answers 2

Reset to default
3

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

Improve this 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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.