MySQL: Return fields where COUNT(*) is greater than

Question

I've got the following SQL, but I only want to return rows where 'hits' are greater than 10.

SELECT clicks.affiliate, COUNT(*) AS hits, affiliates.title, affiliates.url
FROM clicks
INNER JOIN affiliates ON affiliates.id = clicks.affiliate
GROUP BY clicks.affiliate

Thanks.

Answer 1

To filter by an aggregate you need to use the having clause. Unlike many RDBMSs MySQL does allow you to use the column alias in this context (Most other RDBMSs would also insist on affiliates.title, affiliates.url being added to the group by clause as well)

SELECT clicks.affiliate, COUNT(*) AS hits, affiliates.title, affiliates.url
FROM clicks
INNER JOIN affiliates ON affiliates.id = clicks.affiliate
GROUP BY clicks.affiliate
HAVING hits > 10

Answer 2

SELECT clicks.affiliate, COUNT(*) AS hits, affiliates.title, affiliates.url
FROM clicks
INNER JOIN affiliates ON affiliates.id = clicks.affiliate
GROUP BY clicks.affiliate
HAVING COUNT(*) > 10