Get records with more sorting in SQL

Navigation
2

I have two tables: 

comentarios
---id
---comentario
---usuario
---supermercado
---avaliacao

supermercado
---id
---nome
---endereco
---admin

I want to take the average rating of each supermarket and take the 3 supermarkets with the highest average.

Example:

Supermercado01 in the comments table has ratings: 

4, 5, 3, 5 (média então é 4.25)

Supermercado02 in the comments table has ratings: 

1, 1, 1, 1 (média então é 1)

Supermercado03 in the comments table has ratings: 

4, 3, 3, 4 (média então é 3.5)

Supermercado04 in the comments table has ratings: 

1, 5, 2, 2 (média então é 2.5)

SQL should then return me the records of Supermarket01, Supermarket3, and Supermarket4. Could it be done in a SQL only? I did not post any because the ones I tried were pretty flawed, and I was also trying to get the result in PHP, but the performance went awry.

The best attempt was: 

SELECT supermercados.nome, AVG(comentarios.avaliacao) as avaliacao
FROM supermercados, comentarios
WHERE 
supermercados.id = comentarios.supermercado ORDER BY avaliacao
    
asked by anonymous 03.05.2016 / 21:05

2 answers

2

try this: 

SELECT supermercados.nome, AVG(comentarios.avaliacao) as avaliacao
FROM 
    supermercados, comentarios
WHERE 
    supermercados.id = comentarios.supermercado 
group by
    supermercados.nome
ORDER BY 
    AVG(comentarios.avaliacao)
    
03.05.2016 / 21:35
1

Follow this solution: 

SELECT s.nome as 'Supermercado', AVG(c.avaliacao) as 'Média Avaliação' 
    FROM supermercado s 
        INNER JOIN comentarios c 
            ON (s.id = c.supermercado_id) 
                GROUP BY s.nome ORDER BY AVG(c.avaliacao) DESC LIMIT 3;

Basically a merging of tables is done where only supermarkets that have evaluations will be listed. Then the mean is calculated with the values found. The grouping is by name, since the selection brings a field and a function ( Must be grouped whenever a query merge fields and functions ). Finally, it is sorted in decreasing order by the mean and LIMIT is just the three largest averages.

I hope I have helped. :)

    
04.05.2016 / 02:34
Verification javascript [duplicate] Multiple-field sql search in java