SELECT t1.a, t1.b, t1.c, COUNT( t2. * ) AS d
FROM table t1
LEFT JOIN table t2 ON t1.b = t2.c
WHERE t1.b = 'foobar'