获取按ENUM字段分组的COUNT，并用0填充未使用的枚举

Question

假设我有一个表

class UserModel(BaseModel):
id = Column(Integer, primary_key=True)
status = Column(
Enum(
"registered",
"confirmed",
"pending",
name="user_statuses",
)
)

我想查询该表并获得按状态分组的用户的COUNT，如果没有具有特定状态的用户，我仍然想返回它，但返回0。

例如，假设我在数据库中有以下用户：

id    status
1     confirmed
2     confirmed
3     registered

因此，我想得到这样的东西：

status     user_count
registered 1
confirmed  2
pending    0

我怎样才能做到这一点？

Answer 1

您必须使用包含count()的子查询。如果您有status表格(此处为结果(：

select s.status, coalesce(u.user_count,0) as user_count
from status s left join 
(select status,count(*) as user_count from users group by status) u on u.status = s.status

如果您没有status表(此处为结果(：

select s.status, coalesce(u.user_count,0) as user_count
from (select unnest(array['registered','confirmed','pending']) as status) s left join 
(select status,count(*) as user_count from users group by status) u on u.status = s.status