How to union two counts queries in SQLAlchemy?

I have two queries and the only difference between then is that one is counting the success status and the other failure status. Is there a way to get this result in just one query? I’m using SQLALchemy to do the queries.

success_status_query = (
    db_session.query(Playbook.operator, func.count(Playbook.operator).label("success"))
    .filter(Playbook.opname != "failed")
    .join(AccountInfo, AccountInfo.hardware_id == Playbook.hardware_id)
    .group_by(Playbook.operator)
)

failure_status_query = (
    db_session.query(Playbook.operator, func.count(Playbook.operator).label("failure"))
    .filter(Playbook.opname == "failed")
    .join(AccountInfo, AccountInfo.hardware_id == Playbook.hardware_id)
    .group_by(Playbook.operator)
)

Answer

You can use conditions on Count, your query will look like:

query = (
    db_session.query(
        Playbook.operator,
        func.count(
            case(
                [((Playbook.opname != "failed"), Playbook.operator)],
                else_=literal_column("NULL"),
            )
        ).label("success"),
        func.count(
            case(
                [((Playbook.opname == "failed"), Playbook.operator)],
                else_=literal_column("NULL"),
            )
        ).label("failure"),
    )
    .join(AccountInfo, AccountInfo.hardware_id == Playbook.hardware_id)
    .group_by(Playbook.operator)
)