Get COUNT grouped by ENUM field and fill not used enum with 0-CodePudding

Let's say I have a table

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

I wanna query the table and get the COUNT of users grouped by their status and if there is no user with specific status I still wanna return it but with 0.

For instance let's say I have following users in the database:

id    status
1     confirmed
2     confirmed
3     registered

As a result I wanna get something like:

status     user_count
registered 1
confirmed  2
pending    0

how can I achieve such thing?

CodePudding user response：

You have to use subqueries with count() inside. If you have a status table(result here):

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

If you don't have a status table (result here):

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