mysql - 按“状态”和“用户”字段分组

Question

我有像这样的表结构

users(user_id, username);

feedback(id, customer_id, user_id, status)

status会在哪里['scheduled', 'failed', 'cancelled']；

我想要这样的结果

| Username  | scheduled count   | failed count  | cancelled count   |
|---------- |-----------------  |-------------- |-----------------  |
| Sarah     | 10                | 5             | 2                 |
| Alex      | 8                 | 7             | 5                 |

我尝试了很多，但未能通过group by子句获得结果。它根据用户名而不是状态给出结果。

我尝试了一些查询

sql> select users.username, count(*) as cal
from feedback
join users on users.user_id = feedback.user_id
group by feedback.user_id, feedback.status

sql> select users.username, count(*) as cal
from feedback
join users on users.user_id = feedback.user_id
group by feedback.user_id

等等

标签： mysqlsqlgroup-by

Answer 1

您可以进行聚合：

select u.username,
       sum(status = 'scheduled') as scheduled_count,
       sum(status = 'failed') as failed_count ,
       sum(status = 'cancelled') as cancelled_count 
from user u inner join
     feedback f
     on f.user_id = u.user_id
group by u.username;