Maria db how to use groups, unions, and/or sum/count in a select query

Question

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 not working in this one.

I have three tables as follows:

create table zipcode (zip int, city varchar(30))
create table student (id int, zip_fk int)
create table teacher (id int, zip_fk int)

I want to create a select query that will have the following fields: city, the number of students from the city, the number of teachers from the city, and the total number of students and teachers from the city. Essentially, the results should be grouped by city. I am at a complete loss.

Edit. The challenge I am facing is that the city field is located in a different table and is not a primary key or a foreign key. As such, I cannot directly use it. The primary key is zip which means I first have to derive students and teachers from their respective tables, then bring in the zipcode table to compare their zip with cities.

Answer 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;