Amar number of Car - per car type

select


 count(distinct c.id) as carid,
 cc.car_name as carname
 #z.region as Region

 from cars as c

 join car_classes as cc
 on c.car_class_id = cc.id

 join zones as z
 on z.id = c.zone_id

 join car_zone_logs as cz
 on c.id = cz.car_id

 where cz.car_state = 'Normal'
 and z.state = 'normal'

 group by carname