Tabela - lista
id | nota
1 | 10
2 | 8
3 | 7
1 | 8
3 | 9
I want to average the grades of those who have the same ID, and for this I have created the following query:
SELECT AVG(nota) FROM lista GROUP BY id
With this query I wanted to remove all the information from another table from the ids depending on the average they have, for example if the average of an id is greater than 9, the names of those ids that are in another table are listed
Tabela - informacao
id | nome
1 | Teste1
2 | Teste2
3 | Teste3
4 | Teste4
By my research it is necessary to exist a query within another one that will always result in error because it is not possible to compare the average of several results with a number. Remember that I want to use this to list the names of the other table if they have a higher note average. Here is a small example of what I want to do (the example below is wrong):
select informacao.* from informacao, lista where informacao.id like lista.id
and 9<(SELECT AVG(nota) FROM lista GROUP BY id)
At the end of the query I wanted to be able to have the name of all those who had the average grade higher than a chosen value.