select x1.film_id,x3.film_name,count(distinct x1.xn) cc,sum(x1.qty) amount
,sum(if(x1.damage_id>0,1,0)) 'lost'
from xray_report_film x1
left join xray_report x2 on x1.xn=x2.xn
left join xray_film x3 on x1.film_id=x3.film_id
where report_date between '2010-10-1' and '2011-9-30'
group by x1.film_id