mysql 计数 group,mysql group_concat里面有一个计数？

i have 2 tables, users and follows. table follows has a column named status. I would like to count how many follows each user has grouping by the status.

The query below returns a record 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 using group_concat and count but didnt work. Any clues?

解决方案

You need to use GROUP BY twice, first on (user_id, status) from follows to get counts then on user_id from joined table to 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