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, Jul 22 '20 at 20:44
Why can you not make it possible? Should be just `func.count().filter(...)`: https://stackoverflow.com/questions/37328779/sqlalchemy-func-count-on-boolean-column/37333891#37333891 — Ilja Everilä, Jul 22 '20 at 21:44

score 0 · Answer 1 · answered Jul 22 '20 at 20:52

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

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

1 Answers1