SELECT SUM(CASE WHEN xf_reverse = 'N'  THEN xf_incount  ELSE xf_outcount  END) groupincount,

 SUM(CASE WHEN xf_reverse = 'Y' THEN xf_incount ELSE xf_outcount END) groupoutcount, 

xf_storecode storecode, xf_startyear starty, xf_startmonth startm, xf_startday startd FROM

 (SELECT xf_storecode, xf_startyear, xf_startmonth, xf_startday FROM xf_tc_countdata   cd STRAIGHT_JOIN

(SELECT * FROM xf_tc_mapping WHERE 1 = 1 AND (1 = 0 OR xf_storecode = '001000') AND (1 = 0 OR xf_zoneid = '00')) map ON 

cd.xf_machineid = map.xf_machineid AND cd.xf_cameraid = map.xf_cameraid WHERE 1 = 1 AND 

{FN CONCAT({FN CONCAT({FN CONCAT({FN CONCAT(xf_startyear, '-')}, xf_startmonth)}, '-')},xf_startday)} >= '2009-04-01' AND

 {FN CONCAT({FN CONCAT({FN CONCAT({FN CONCAT(xf_endyear, '-')}, xf_endmonth)}, '-')},xf_endday)} <= '2009-04-20' AND 

{FN CONCAT(xf_starthour, xf_startminute)} >= '0000' AND {FN CONCAT(xf_starthour, xf_startminute)} < '2400' 

GROUP BY xf_storecode, xf_startyear, xf_startmonth, xf_startday ) countdata  

GROUP BY xf_storecode, xf_startyear, xf_startmonth, xf_startday