SELECT t1.`name`, t2.`state`, t2.`count` FROM(SELECT `id`, `name` FROM `ost_department`) t1LEFT JOIN(SELECT `dept_id`, `state`, COUNT(*) AS `count` FROM `ost_thread_event`GROUP BY `dept_id`, `state`) t2ON t1.`id` = t2.`dept_id`WHERE t2.`state` IN ('transferred')