SQL programming

Question

how can I determine the number of CoE students per school per city?

the coe students belongs to a different table from the school table from the city table. I really need some help now. thanks

Answer 1

You'll need to use a JOIN to link the tables, GROUP BY on School and city and COUNT Without further details of your table structure I can't give you a specific query to use!

Answer 2

Go step by step: start listing all the students. Then try listing all the students and their school, by finding what is the relationship between the student table and the school table. Then try listing all students with their school and city.

Once you've worked out how to join these tables, you can search how to aggregate the results to get the required output.

Answer 3

I assume that there is a relation between the school and the student tables, and one between the school and the city tables, as that it that makes sense.

Join the tables together, group on the school and the city, and count the students. Something like:

select sc.Name, c.Name, count(*) as students
from Student st
inner join School sc on sc.Id = st.SchoolId
inner join City c on c.Id = sc.CityId
group by sc.Name, c.Name