mysql - 对多列进行计数和分组

我从当前查询中获得了这些数据

Status Name
1      Lester Boyer
2      Lester Boyer
4      Lester Boyer
1      Lester Boyer
2      Lester Boyer
0      Lester Boyer
3      Lester Boyer
1      Lester Boyer
1      Rosalinda Marks
2      Rosalinda Marks

但是我想计算该用户的状态数，因此最终表应如下所示：

Status Name            Count
0      Lester Boyer    1
1      Lester Boyer    3
2      Lester Boyer    2
3      Lester Boyer    1
4      Lester Boyer    1
1      Rosalinda Marks 1
2      Rosalinda Marks 1

当前查询是

SELECT allocation.status, CONCAT(profile.forename, ' ', profile.surname) AS name
FROM allocation
  INNER JOIN user ON allocation.user_id = user.id
  INNER JOIN profile ON user.id = profile.user_id

最佳答案

您似乎在查询中不需要user表。您的版本只需要一个group by：

SELECT a.status, CONCAT(p.forename, ' ', p.surname) AS name, COUNT(*)
FROM allocation a INNER JOIN
     profile
     ON p.user_id = a.user_id
GROUP BY a.status, CONCAT(p.forename, ' ', p.surname) ;