SELECT C.id_citta, C.citta,
       COUNT(A.id) AS cnt
FROM italy_cities C
  LEFT JOIN azienda A ON A.citta = C.citta
WHERE 1
  AND C.provincia = 'CH'
  AND A.attiva = 1
GROUP BY C.id_citta
ORDER BY C.citta