MySQL query to output variables grouped by entry status

Question

I have schools, teachers and students channels with each of the students and teachers channels with a field that contains the entry_id of the school they're related to (not actually using native EE relationships fields in this case).

I need to output a list of all the schools and their total number of teachers and students but also show which of those entries are open and pending (entries only become open after being activated via link in an invite email).

What I have so far outputs the school and the count of all students but I need to know how to change it so that I can output different variables for all students and pending ones.

{exp:channel:entries
    channel="schools"
    disable="categories|member_data|pagination|trackbacks"
    dynamic="no"
    orderby="title"
    sort="asc"
}

{exp:query sql="SELECT COUNT(cd.entry_id) as student_count
    FROM exp_channel_titles ct
    JOIN exp_channel_data cd ON cd.entry_id = ct.entry_id
    WHERE cd.channel_id=5
    AND cd.field_id_45={entry_id}
}

{if count==1}<p><strong>Total schools:</strong> {total_results}</p>
<ul>{/if}

    <li>{title} – {student_count}student{if student_count!=1}s{/if}</li>

{if count==total_results}</ul>{/if}

{/exp:query}

{/exp:channel:entries}

I'd also like to know the most optimal way to query the records from both students and teachers channels. I could double up on queries and just change the channel_id and field_id_XX but would like to know if there's a cleaner method.

Answer 1

Found an answer to my question:

{exp:query sql="SELECT
    SUM(ct.status = 'open' OR ct.status = 'pending') student_count,
    SUM(ct.status = 'pending') student_pending
    FROM exp_channel_titles ct
    JOIN exp_channel_data cd ON cd.entry_id = ct.entry_id
    WHERE cd.channel_id=5
    AND cd.field_id_45={entry_id}"
}

At the moment I've still got two separate queries counting from the two different channels so I'd still like to know if there's a way of combining the two.