How to combine these three queries?-CodePudding

How to combine all the queries below in a single query using joins? Need to get columns student_id, all_tasks, failed, passed.

select 
    s.id as student_id,
    count(st.student_id) as all_tasks   
from accounts_user s, tasks_studenttask st
    where s.id = st.student_id group by s.id;


select 
    s.id as student_id,
    count(st.student_id) as not_accepted
from accounts_user s, tasks_studenttask st
    where s.id = st.student_id and st.status = 2
group by s.id;


select 
        s.id as student_id,
        count(st.student_id) as accepted
from accounts_user s, tasks_studenttask st
        where s.id = st.student_id and st.status = 1
group by s.id;

Output should be like this

CodePudding user response：

First, stop using these old style implicit joins. Use an actual JOIN:

select 
    s.id as student_id,
    count(st.student_id) as all_tasks   
from accounts_user s INNER JOIN  tasks_studenttask st
    ON s.id = st.student_id group by s.id;


select 
    s.id as student_id,
    count(st.student_id) as failed
from accounts_user s INNER JOIN  tasks_studenttask st
    ON s.id = st.student_id 
where  st.status = 2
group by s.id;


select 
        s.id as student_id,
        count(st.student_id) as passed
from accounts_user s INNER JOIN tasks_studenttask st
        ON s.id = st.student_id 
where st.status = 1
group by s.id;

This helps separate join criteria from WHERE predicates which makes it clear that these two tables are joined the same each time. Our resulting combined SQL likely just needs this one join. The WHERE predicates can be moved up into CASE expressions since they are only present to delineat all_task vs failed vs passed. So:

SELECT
    s.id as student_id,
    count(*) as all_tasks,
    count(CASE WHEN st.status = 2 THEN student_id END) as failed,
    count(CASE WHEN st.status = 1 THEN student_id END) as passed
FROM accounts_user s 
    INNER JOIN tasks_studenttask st
        ON s.id = st.student_id
GROUP BY s.id;