MySQL Easy more than function

Question

I am trying to do this:

SELECT Filmai.Pagrind_atlik, ( COUNT(Filmai.Pagrind_atlik)) AS Kiek_kartu_filmavosi 
FROM Filmai 
WHERE  Kiek_kartu_filmavosi >2 
GROUP BY Filmai.Pagrind_atlik;   

But it wont allow me to make >2 thing. So maybe any suggestions?

Answer 1

You need to use HAVING clause

SELECT Filmai.Pagrind_atlik, 
( COUNT(Filmai.Pagrind_atlik)) AS Kiek_kartu_filmavosi
FROM Filmai  
GROUP BY Filmai.Pagrind_atlik
HAVING Kiek_kartu_filmavosi >2;

WHERE is applied before GROUP BY, HAVING is applied after (and can filter on aggregates).

Answer 2

WHEN you want to limit an aggregate function's results you can't use it in the WHERE clause. You have to use HAVING:

SELECT Filmai.Pagrind_atlik,
  COUNT(Filmai.Pagrind_atlik) AS Kiek_kartu_filmavosi
FROM Filmai
GROUP BY COUNT(Filmai.Pagrind_atlik)
HAVING COUNT(Filmai.Pagrind_atlik) > 2