MySQL count a field into two different count results

Question

I'm trying to COUNT attendance_status of drivers according to its value. this is my code as of moment.

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

I'd like to know how to make another COUNT of attendance_status when it's value is 4 using a single query.

Can you post your table structure with sample input data and desired result? — Saharsh Shah
– Saharsh Shah, Commented Jan 9, 2015 at 7:31

Saharsh Shah · Accepted Answer · 2015-01-09 07:43:57Z

2

Try this:

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;

answered Jan 9, 2015 at 7:43

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shadeofblack Over a year ago

it worked! thanks man, even if i failed to post the structure cause you've already answered it. uhm, additional question, what does the part--> THEN 1 ELSE 0 END meant?

Abecee Over a year ago

This defines the outcomes of the CASE: 1 for the value actually checked for (here 19 and 4), 0 for anything ELSE. END is the end of the CASE.

Collectives™ on Stack Overflow

MySQL count a field into two different count results

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related