I am trying to calculate the average points of a table called recipes, each recipe may or may not be rated and may be rated by multiple users.
In the image you can see that I get the points for each recipe, but this causes the recipes to be duplicated.
What I want is for it to show me the recipe with the average of points out of 10 instead of the points that a user gave it.
The relationship between the tables is as follows:
The sql query I have is the following:
SELECT usuarios.nombre AS usuarios,
recetas.id_usuario AS id_Usuario,
recetas.nombre AS recetas,
recetas.id AS id_Receta,
recetas.preparacion,
recetas.fecha_creacion,
recetas.imagen,
recetas.inf_nutricional,
recetas.ingredientes,
recetas.descripcion,
tipo.nombre AS tipo,
tipo.color,
valorar.puntos
FROM recetas
JOIN usuarios ON recetas.id_usuario = usuarios.id
JOIN recetas_tipo ON recetas.id = recetas_tipo.id_recetas
JOIN tipo ON recetas_tipo.id_tipo = tipo.id
LEFT JOIN valorar ON recetas.id = valorar.id_receta
ORDER BY recetas.id;
You would need to group by the data of the recipe and calculate the average of the evaluation, taking into account that you may not have points, the recipe would not have received evaluation.
You need to group by those fields you need to retrieve, and apply the AVG function to the score. Since the points column can be null, you can apply the COALESCE function that evaluates the first expression that is not null, in this case it would be 0.