3
I have this query here that is pulling right. However, now I want to put those who have the registration only within the current month (for example September). My created_at
is datetime
. I’m inserting WHERE "participantes_atividades.created_at" BETWEEN "2019-09-01" AND LAST_DAY("2019-09-01")
, but it’s giving error. My current code is like this:
'SELECT participantes.name, COUNT(participantes_atividades.atividade_id) AS qtd
FROM participantes_atividades
JOIN participantes ON participantes.id = participantes_atividades.participante_id GROUP BY participantes_atividades.participante_id, participantes.name
ORDER BY 2 DESC'
I am working with Laravel framework and am getting the following error:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN participantes ON participantes.id = participantes_atividades.participante_i' at line 4 (SQL: SELECT participantes.name, COUNT(participantes_atividades.atividade_id) AS qtd FROM participantes_atividades WHERE `participantes_atividades.created_at` BETWEEN "2019-08-01" AND "2019-08-30" JOIN participantes ON participantes.id = participantes_atividades.participante_id (GROUP BY participantes_atividades.participante_id), participantes.name ORDER BY 2 DESC)```
Remove these parentheses involving the GROUP BY clause. Also note that you are grouping by participant_activities.participante_id but this field is not in the SELECT list.
– anonimo
Sorry. Parentheses was my test.. I removed and is still giving error.
– Vitor de Sousa
But you keep putting a field in the GROUP BY clause that is not in the SELECT field list. I also don’t understand why the error message displays a different SQL command than you put in the question.
– anonimo
I am in need of grouping the amount of activities a participant has to be able to make a ranking. That’s why
Group by
.– Vitor de Sousa
But you are grouping by participant_activities.participante_id and this field is not in your SELECT. If you do not want the participating field_activities.participante_id then group only by participants.name.
– anonimo
Yeah. on Count... Actually I don’t need the ID. I just need the amount of activities and the name of the participant... is what you’re bringing in these days. But now, I just need to know these results, just the ones that were created in the current month.
– Vitor de Sousa
If you are in the aggregation function then you should not be in the GROUP BY clause.
– anonimo