Let's start with such example:

I have 2 tables: Therapists and their Sessions.

I have such query

select therapists.id, s1.completed_count, s2.canceled_count
from therapists
         inner join (select therapist_id, count(*) as completed_count
                     from sessions
                     where sessions.status = 'complete'
                     group by therapist_id) s1 on s1.therapist_id = therapists.id
         inner join (select therapist_id, count(*) as canceled_count
                     from sessions
                     where sessions.status = 'canceled'
                     group by therapist_id) s2 on s2.therapist_id = therapists.id;

Is it possible to count sessions like this with single subquery join? Because I have several statuses and don't think that is ok to performance to write so many joins.

CodePudding user response：

If you have a static number of statuss, numerous JOINs are fine. Alternatively, you could consider something along the lines of crosstab to "pivot" a row result to a column result. See PostgreSQL: tablefunc Module.

If you have a dynamic number of statuss, you could perhaps leverage array_agg to convert row results to an array. See PostgreSQL: Array Functions and Operators.

CodePudding user response：

Simple

SELECT
    therapists.id,
    COUNT(
        CASE WHEN status = 'complete' THEN 1 END
    ) AS completed_count,
    COUNT(
        CASE WHEN status = 'canceled' THEN 1 END
    ) AS canceled_count
FROM therapists t
JOIN sessions s ON s.therapist_id = t.id
GROUP BY t.id
;