List in Mysql tables that do not have AVG

Question

I have two tables in Mysql like this:

LOCAL

id | nome

NOTA_LOCAL (user notes for each location)

id | id_local | nota

I’m listing the locations and already showing the average of notes that each location receives, so:

SELECT *, AVG(local_nota.nota) AS 'nota' FROM local
LEFT JOIN local_nota ON local.id = local_nota.id_local
GROUP BY local_nota.id_local
ORDER BY local.nome ASC

The places that have notes he lists but does not list the places that have not yet received notes, what to change in the code?

Answer 1

The problem was in GRUP BY, I changed to group to LOCAL id, like this:

SELECT *, AVG(local_nota.nota) AS 'nota' FROM local
LEFT JOIN local_nota ON local.id = local_nota.id_local
GROUP BY local.id
ORDER BY local.nome ASC

Answer 2

Try to do it this way

SELECT local.nome, AVG(nota_local.nota) AS nota FROM local LEFT JOIN nota_local ON local.id = nota_local.id_local GROUP BY local.nome ORDER BY local.nome