select p.*, count(m.seq) as pmsFailCnt from ( select p.seq as plantSeq, p.name as plantName, p.supplyPower, min(d.data15) as data15Min, max(d.data15) as data15Max, round(avg(d.data15)) as data15Avg, min(d.data32) as data32, max(d.data33) as data33, min(d.data16) as data16, min(d.pvPower) as pvPowerMin, max(d.pvPower) as pvPowerMax, round(avg(d.pvPower)) as pvPowerAvg, (select count(t.seq) from t_timetable_pms t where t.inputDate between #{startDate} and #{endDate} ) as pmsCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate between #{startDate} and #{endDate} ) as kescoCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate between #{startDate} and #{endDate} and k.sendStatus = '00' ) as kescoFailCnt from t_plant p, t_pmsdata_statistic_day d where p.seq = d.plantSeq and d.inputDate between #{startDate} and #{endDate} group by p.seq ) p left join t_pmsdata_${searchYear} m on p.plantSeq = m.plantSeq and m.inputDate between #{startDate} and #{endDate} and (m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0 or m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0) group by p.plantSeq select p.name as plantName, d.* from ( select plantSeq, CAST(SUM(CASE WHEN typeCode ='10' THEN modelCode END ) AS CHAR(2)) as device10, CAST(SUM(CASE WHEN typeCode ='20' THEN modelCode END ) AS CHAR(2)) as device20, CAST(SUM(CASE WHEN typeCode ='30' THEN modelCode END ) AS CHAR(2)) as device30, SUM(CASE WHEN typeCode ='30' THEN quantity ELSE 0 END ) as device30Quantity, CAST(SUM(CASE WHEN typeCode ='40' THEN modelCode END ) AS CHAR(2)) as device40, SUM(CASE WHEN typeCode ='40' THEN quantity ELSE 0 END ) as device40Quantity from t_device group by plantSeq ) d, t_plant p where d.plantSeq = p.seq and p.plantStatus='01'