how to bring ten best media in mysql?

Asked

Viewed 176 times

0

I have two tables: participants and results. In the table of participants I have idParticipant as pk and name; in the table results I have idParticipante as fk, result and date.

For the result to enter the ranking participants must report 3 results per day (date field) but can report less (client rule). Each day will have an average adding up the three results and dividing by three. In the end, the bank will have 15 days of each participant, but I need to bring the top 10 (smallest) average of each participant. Remembering that can only count the days when the participant sent three results.

I did the query below that brings all the averages on the date the participant sent three results, but wanted a way to list only 10 days, instead of all. Is it possible that or I’ll have to return all the lines and eliminate the worst ones by PHP?

 SELECT p.idParticipante, p.nome, g.data,
 ROUND(SUM( g.resultado /3),1) AS media,
 COUNT( g.resultado ) AS participacoes
 FROM participantes p
 JOIN resultados g ON p.idParticipante = g.idParticipante
 GROUP BY p.idParticipante, g.data
 HAVING participacoes = 3
 ORDER BY p.idParticipante, media
mysql sql

  • Mysql has the function AVG() that returns the average of an expression, you may consider using it if it fits your case.

  • See this question (in English): https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group

  • But if I use limit 10, I’ll only have 10 records in total, but what I want are the ten lowest average for each participant

  • Sorry then rsrs confused me with the phrase "but wanted a way to list only 10 days, instead of all." Could put a visual example of these tables?

  • Using the limit is an idea proposed by the author of the question, the other users explain that the solution is not so simple as to provide alternatives.

  • @Piovezan I proposed limit earlier in the comments but clarified to zoom in on the answer. I am now changing to meet the criteria

  • @Gustavofragoso use limit is not the solution. Read the English question Linkei. Both want N results from each group (i.e., from each participant).

Show 2 more comments

1 answer

0

To list only the last 10 days, you can use the function DATE_ADD to subtract 10 days from the current date and compare with the date of your registration. Your query would look like this:

SELECT p.idParticipante, p.nome, g.data,
ROUND(SUM( g.resultado /3),1) AS media,
COUNT( g.resultado ) AS participacoes
FROM participantes p
JOIN resultados g ON p.idParticipante = g.idParticipante
WHERE g.data >= DATE_ADD(NOW(), INTERVAL -10 DAY)
GROUP BY p.idParticipante, g.data
HAVING participacoes = 3
ORDER BY p.idParticipante, media

  • I don’t want the last ten days, I want the lowest ten average of each participant

  • But in your question you say Fiz a query abaixo que traz todas as médias na data em que o participante enviou três resultados, mas queria uma forma de listar só 10 dias, invés de todos.

  • Yes. If I have 15 dates from two participants, for example, I will have 30 records. If I use a limit 10, I will stop at the first ten records of the first participant and I will not bring anything from the second

  • Put the structure of these two tables and the data into http://sqlfiddle.com/, so we can test some querys.

  • Putz... I’ll just converge put by night

Browser other questions tagged mysql sql

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