Get more SQL-rated records

Asked

Viewed 124 times

2

I have two tables:

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

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

I want to take the average of ratings from each supermarket and get the 3 supermarkets with the highest average.

Example:

Supermercado01 in the table reviews have reviews:

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

Supermercado02 in the table reviews have reviews:

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

Supermercado03 in the table reviews have reviews:

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

Supermercado04 in the table reviews have reviews:

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

SQL should then return me the records of Supermercado01, Supermercado03 and Supermercado04. Could it be done in one SQL? I didn’t post any because the ones I tried were quite flawed, and I was also trying to get the result in PHP, but the performance got rough.

The best attempt was:

SELECT supermercados.nome, AVG(comentarios.avaliacao) as avaliacao
FROM supermercados, comentarios
WHERE 
supermercados.id = comentarios.supermercado ORDER BY avaliacao
mysql sql

2 answers

2


try like 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)

  • Solved the case, what would be the effect of GROUP BY ? I can’t remember how it’s used.

  • 1

    Group by group by field(s) reported. There the aggregation functions (AVG, SUM, COUNT, etc.) are relived in the set of records where the reported fields are equal. In this case for all supermarkets of the same name.

1

Follows 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 junction is made of the tables where only supermarkets that have reviews will be listed. Then the average is calculated with the values found. The grouping is by name, since the selection brings a field and a function (It should be grouped whenever a query merges fields and functions). Finally it is ordered in a decreasing way by the average and the LIMIT brings precisely the three highest averages.

I hope I’ve helped. :)

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.