when I add COUNT in the WHERE clause it produces an error but works well in Select

Question

Trying to understand why this code produces a result but when I add COUNT(IdCritique) in WHERE it returns an error???

SELECT Titre, ROUND(AVG(Etoiles)) AS Moyenne, COUNT(IdCritique) AS Critiques2020
FROM Film
JOIN Critique
ON FilmID = IdFilm
JOIN Plateforme
ON PformeID = IdPforme
WHERE YEAR(Date) = "2020"  
GROUP BY FilmID;

you cannot use aggregated function in where clause, use having clause — Fahmi, Jul 01 '20 at 06:53

score 0 · Answer 1 · answered Jul 01 '20 at 06:56

Try like this -

SELECT Titre, ROUND(AVG(Etoiles)) AS Moyenne, COUNT(IdCritique) AS Critiques2020
FROM Film JOIN Critique ON FilmID = IdFilm
JOIN Plateforme ON PformeID = IdPforme
WHERE YEAR(Date) = "2020"  
GROUP BY FilmID
having  COUNT(IdCritique)= your condition

when I add COUNT in the WHERE clause it produces an error but works well in Select

1 Answers1