I have got the next SQL, however i simply want to return rows where 'hits' are more 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.

To filter by an aggregate you should utilize the having clause. Unlike many RDBMSs MySQL does permit you to make use of the column alias within this context (Other RDBMSs would also insist upon affiliates.title, affiliates.url being put into the group by clause too)

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
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
 ...
HAVING hits > 10