filter between two dates with Join

Question

I have two tables: col (collaborator) and Indicacao.

I made a select to know how many referrals each employee has. It’s working but I can’t filter by date using between.

SELECT c.id, c.nome, funcao, area, count(p.cpf_cli) AS quantidade FROM 
col c LEFT JOIN
indicacao p ON p.id_colaborador = c.id GROUP BY c.id, c.nome ORDER BY
c.nome

I tried with between but it didn’t work.

SELECT c.id, c.nome, funcao, area, count(p.cpf_cli) AS quantidade FROM 
col c LEFT JOIN
indicacao p ON p.id_colaborador = c.id GROUP BY c.id, c.nome ORDER BY
c.nome WHERE indicacao.data_ind BETWEEN `2018/09/20` AND '2018/09/21'

Answer 1

Always remember that reserved words like GROUP BY and ORDER BY comes after the WHERE.

Another thing is that every column that is not being used in grouping functions with SUM, COUNT, AVG etc, need to be listed in the clause GROUP BY.

SELECT 
   c.id, 
   c.nome, 
   funcao, 
   area, 
   count(p.cpf_cli) AS quantidade 
FROM col c 
LEFT JOIN indicacao p 
   ON p.id_colaborador = c.id 
WHERE 
   indicacao.data_ind BETWEEN `2018/09/20` AND '2018/09/21'
GROUP BY 
   c.id, c.nome 
ORDER BY
   c.nome