SELECT d.id AS `Department ID` , d.name AS `Department Name` , COUNT( t.id ) AS `Unresolved Tickets Count`
FROM hb_tickets t
JOIN hb_ticket_departments d ON ( t.dept_id = d.id )
WHERE t.status != 'Closed'
GROUP BY t.dept_id