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.
x
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)
)
Advertisement
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)
)