Maria Db How To Use Groups, Unions, And/or Sum/count In A Select Query

I've been breaking my mind for three days trying to puzzle this one out. I'm new to Maria db and sql in general. I've managed to use UNION on a previous similar situation but it's

Solution 1:

This is rather tricky. Here is one method using union all and group by:

select city, sum(student) as students, sum(teacher) as teachers
from ((select z.city, 1 as student, 0 as teacher
       from student s join
            zipcode z
            on s.zip_fk = z.zip
      ) union all
      (select z.city, 0 as student, 1 as teacher
       from teacher t join
            zipcode z
            on t.zip_fk = z.zip
      )
     ) st
group by city;