mysql foreign key

Question

someone could help me assemble this query?

let’s say I have the following tables:

tabela1: aluno (id, nome, cidade)
1 Pedro
2 Joao
3 Joaquim

tabela2: cidade (id, municipio)
1 Manaus
2 São Paulo
3 Rio de Janeiro

tabela3: cursos (id, nome)
1 Excel
2 Word
3 Power Point

tabela4: aluno_cursos (id_aluno, id_cursos) -> aqui tenho os cursos q um aluno já realizou
1, 1
1, 2
1, 3
2, 2
3, 1
3, 3

now comes the question. How do I search, through a group by, the total of students who held each course, only in the municipality of São Paulo

my report needs to return, for example: students from the municipality of São Paulo (Where municipio = 2) performed: Word: 3 Excel: 2 Power Point: 3

seria algo do tipo select SUM(cursos) from aluno where aluno.municipio = 2 group by cursos

I still don’t understand how sum and group by

Answer 1

I believe it’s something like that.

select count(ac.id_aluno), ac.id_cusos from aluno_cursos as ac
join aluno as al on (al.id = ac.id_aluno and al.cidade = 2)
group by ac.id_cursos