SELECT DATE_FORMAT(`arrivaltime`, '%Y-%m-%d') AS days,COUNT(deviceSerial) AS dayPatrorNum ,COUNT(deviceid) AS countPatror,
sum(case when status='1' then 1 else 0 end ) AS abnormal
FROM patrol_record
GROUP BY DATE_FORMAT(`arrivaltime`, '%Y-%m-%d')
ORDER BY arrivaltime DESC