How can i make a count with different filters in SQLAlchemy - Python

Question

The main problem is to count different elements with Sqlalchemy at the same time. I want to achieve the following SQL.

SELECT
        metrics_stages.job_id,
        COUNT(*) FILTER(WHERE metrics_stages.stage = 'Submitted') AS submitted_count,
        COUNT(*) FILTER(WHERE metrics_stages.stage = 'Applied') AS applied_count,
        COUNT(distinct metrics_stages.talents_job_id) FILTER(WHERE metrics_stages.if_interview = true) AS interview_count
    FROM metrics_stages
    GROUP BY metrics_stages.job_id

But i can not make it possible.

Why don't you try something like SUM(IF(stage = 'Submtted',1,0)) as submitted_count — Omar Alvarado
– Omar Alvarado, Commented Jul 22, 2020 at 20:44
Why can you not make it possible? Should be just func.count().filter(...): stackoverflow.com/questions/37328779/… — Ilja Everilä
– Ilja Everilä, Commented Jul 22, 2020 at 21:44

Omar Alvarado · Accepted Answer · 2020-07-22 20:52:40Z

0

I am not sure what database you are using, in MariaDB and MySQL you can do it like this.

SELECT
    metrics_stages.job_id,
    SUM(IF(metrics_stages.stage = 'Submitted',1,0)) AS submitted_count,
    SUM(IF(metrics_stages.stage = 'Applied',1,0)) AS applied_count,       
    COUNT(distinct IF(metrics_stages.if_interviewed = true, metrics_stages.talents_job_id, NULL)) AS interview_count
FROM metrics_stages
GROUP BY metrics_stages.job_id

answered Jul 22, 2020 at 20:52

Omar Alvarado

1,9242 gold badges15 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

How can i make a count with different filters in SQLAlchemy - Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related