Remove data from one table from the average of notes in another table?

0
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.

    
asked by anonymous 13.05.2017 / 23:29

1 answer

0

Select *, (select avg (note) from list where lista.id = information.id) the media  from information

    
14.05.2017 / 00:05