Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
791 views
in Technique[技术] by (71.8m points)

mysql group_concat with a count inside?

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...