Creating a counting column query-CodePudding

I'm looking to create a select query that will have a column that counts the number of times someone has been trained in a certain month. I have managed to get it to show the list of people who went to training sessions in that specific month, but I want to show everyone else as well with a 0 count of the sessions they attended. How would I do this?

SELECT 
    carer.carer_firstname, carer.carer_lastname,   
    COUNT(carer_training_link.carer_id) AS sessions_attended_May
FROM 
    carer, carer_training_link 
WHERE 
    carer.carer_id = carer_training_link.carer_id 
    AND carer_training_link.training_date BETWEEN TO_DATE('01/MAY/2019', 'DD/MON/YYYY') 
                                              AND TO_DATE('01/JUN/2019', 'DD/MON/YYYY')
GROUP BY 
    carer.carer_firstname, carer.carer_lastname;

CodePudding user response：

You are using a join sytax that can only be called antique.

You want an outer join, so as to include carers that had no training.

Date ranges are best compared with >= and <.

SELECT 
  c.carer_firstname, c.carer_lastname,   
  COUNT(cl.carer_id) AS sessions_attended_may
FROM carer c
LEFT OUTER JOIN carer_training_link cl
  ON cl.carer_id  = c.carer_id
  AND cl.training_date >= DATE '2019-05-01'
  AND cl.training_date <  DATE '2019-06-01'
GROUP BY c.carer_firstname, c.carer_lastname
ORDER BY c.carer_firstname, c.carer_lastname;