-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
SELECT x.f1,IFNULL(y.n,0) AS n
FROM (SELECT DISTINCT f1 FROM t1) x
LEFT JOIN (SELECT f1, COUNT(f2) AS n
FROM t1
WHERE f1 > 5
GROUP BY f1) y
ON x.f1=y.f1;

