SELECT b.sube_id, b.sube_adi,COUNT(a.urun_id) as urun_sayisi FROM urunler as a inner join subeler as b on a.sube_id = b.sube_id
GROUP BY b.sube_id, b.sube_adi