SELECT count(*) as numero, cat.id, cat.nome FROM prodotti as pro JOIN categorie as cat WHERE cat.id=pro.id_categoria GROUP BY cat.id