phpmyadmin Question posted in
The official documentation can be found here.

Condition Where count number of shifts – Phpmyadmin

I have a table “rota” where volunteers submit the shifts they are available. They can submit more than 6 shifts, but 6 is the minimum required.

I want to see how many volunteers have selected each of the shifts, but I want to see only those who have selected 6 shifts.

I am running a wrong SQL, but can-t figure it out what I am doing wrong:

SELECT COUNT(volunteer_id), shift_id 
FROM rota 
GROUP BY shift_id 
WHERE COUNT(shift_id = 6);

EDIT:
I wrongly explained what I was trying to do.

I want to see how many volunteers have selected each of the shifts, but I want to see only those shifts who have been selected by volunteers who submitted only 6 shifts. Does it make sense?

EDIT:

Tried this but not working:

SELECT COUNT(volunteer_id), shift_id 
FROM rota
WHERE volunteer_id IN (SELECT volunteer_id FROM rota
GROUP BY volunteer_id HAVING COUNT DISTINCT(shift_id) = 6)
GROUP BY shift_id;
Tags:

2

Answers


  1. 0

    You can try below –

    SELECT volunteer_id,COUNT(shift_id )
FROM rota 
GROUP BY volunteer_id
having COUNT(shift_id )= 6
    Login or Signup to reply.
  2. 0

    Use a HAVING clause:

    SELECT COUNT(volunteer_id), shift_id 
FROM rota
WHERE volunteer_id IN (SELECT volunteer_id FROM rota
                       GROUP BY volunteer_id HAVING (COUNT DISTINCT shift_id) = 6)
GROUP BY shift_id 
HAVING COUNT(volunteer_id) = 6;

    Assertions which take place after GROUP BY has happened need to appear in a HAVING clause. HAVING operates on groups of records. The WHERE clause, on the other hand, is evaluated before GROUP BY, and operates on individual records.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search