MySQL将一个字段统计成两个不同的统计结果

Question

我正在尝试根据其值计算司机的 attendance_status。这是我目前的代码。

SELECT *, COUNT(attendance_status) AS total_cars_dispatched
FROM driver_attendance da 
LEFT JOIN collectible co ON (da.driver_attendance_id=co.driver_attendance_id)
LEFT JOIN driver_pondo dp ON (dp.collectible_id=co.collectible_id)
WHERE attendance_status=19 AND company_id=84 GROUP BY attendance_date DESC

我想知道当 attendance_status 的值为 4 时，如何使用单个查询对其进行计数。

Answer 1

试试这个:

SELECT attendance_date, 
       SUM(CASE WHEN attendance_status = 19 THEN 1 ELSE 0 END) AS total_cars_dispatched, 
       SUM(CASE WHEN attendance_status = 4 THEN 1 ELSE 0 END) AS attendance_status_4
FROM driver_attendance da 
LEFT JOIN collectible co ON da.driver_attendance_id=co.driver_attendance_id
LEFT JOIN driver_pondo dp ON dp.collectible_id=co.collectible_id
WHERE company_id=84 
GROUP BY attendance_date DESC;