How to union two counts queries in SQLAlchemy?

Question

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)
)

Bugui · Accepted Answer · 2022-08-13 23:48:46Z

2

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)
)

edited Aug 13, 2022 at 23:48

Bugui

195 bronze badges

answered Aug 13, 2022 at 0:06

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fabiodearaujo · Accepted Answer · 2022-08-12 23:41:01Z

0

you can use the or_() operator like the sample bellow:

from sqlalchemy import or_

stmt = select(users_table).where(
                or_(
                    users_table.c.name == 'wendy',
                    users_table.c.name == 'jack'
                )
            )

It won't be a straight swap, but you will be able to work it out.

You can find more information in the SQLAlchemy documentation: https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.or_

answered Aug 12, 2022 at 23:41

fabiodearaujo

11 bronze badge

Collectives™ on Stack Overflow

How to union two counts queries in SQLAlchemy?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related