Mysql group_concat with account inside?

Question

I have 2 tables, and users follow. The table below shows a column called status. I would like to calculate how many of them should be followed by each user, grouping by status.

The query below returns an entry for each status type for each user.

SELECT users.name as user_name, f.status, count(f.id) FROM users JOIN application_follows f ON f.user_id = users.id GROUP BY users.id, f.status ORDER BY users.id 

returns something like:

 user_name status count mike new 10 mike old 5 tom new 8 tom old 9 

but I would like something more friendly, like:

 user_name status_count mike new,10|old,5 tom new,8|old,9 

tried to use group_concat and count, but didn't work. Any clues?

Answer 1

You need to use GROUP BY twice, first on (user_id, status) to get counters, and then on user_id from the joined table in concat:

 SELECT users.name, GROUP_CONCAT( CONCAT(f.status, ',', f.cnt) SEPARATOR '|' ) FROM users JOIN ( SELECT user_id, status, count(id) AS cnt FROM application_follows GROUP BY user_id, status ) f ON f.user_id = users.id GROUP BY users.id 

Answer 2

I don’t know the definition of full tables, so I created a query that uses only username, status and account.

 SELECT user_name, GROUP_CONCAT(CONCAT(status, ',', count) SEPARATOR '|') FROM users GROUP BY user_name ORDER BY user_name;