Insert WHERE into query giving error

Asked

Viewed 48 times

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)```
php mysql sql mariadb
  • 1

    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.

  • Sorry. Parentheses was my test.. I removed and is still giving error.

  • 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.

  • 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.

  • 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.

  • 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.

  • If you are in the aggregation function then you should not be in the GROUP BY clause.

Show 2 more comments

2 answers

3


Friend, in the select that is shown in your error, in addition to missing the field participante_id you’re putting the condition where in the wrong order, try so:

SELECT participantes.name, participantes_atividades.participante_id, COUNT(participantes_atividades.atividade_id) AS qtd 
        FROM participantes_atividades 
        JOIN participantes ON participantes.id = participantes_atividades.participante_id
        WHERE participantes_atividades.created_at BETWEEN "2019-08-01" AND "2019-08-30"
        GROUP BY participantes_atividades.participante_id, participantes.name ORDER BY 2 DESC

  • Thanks Murilo! Your answer is almost right.. I just needed to change the ORDER BY for 3. But it worked right. Thanks anyway.

1

You must add in the clause select the field "participantes_atividades.participante_id" in order to group by it.

SELECT participantes.name, participantes_atividades.participante_id, 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;

  • Hi Leonardo, sorry.. I don’t understand.. I’m only trying to get the results of the current month. this code brings everyone as is already.

  • I just sent you an example, the change to your code was just the first line. Do the following: add ", participations_activities.participante_id" to the select line and be happy.

Browser other questions tagged php mysql sql mariadb

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