I need to obtain the list of patients from a doctor, and in that list totalize the times that patient has been treated with that doctor (status = 4), but there are patients who have not yet been seen by the doctor because they have the time in the pending state or have the time canceled. Patients with canceled or pending hours must also appear in the list, and in the total column should appear as 0 or null
With the answers and comments received, I have achieved the following query
select count(hr.paciente_id) as total, p.nombre_completo from pacientes p left outer join horas hr on p.id = hr.paciente_id where hr.medico_id = 1 and (hr.estado = 4 or hr.paciente_id is not null) group by p.id order by p.nombre_completo
The total number of patients who give me the consultation is the right one, but I'm leaving in 1 patients who have canceled or pending hours.
For example, row two, should be 0 or null or empty (any of these values is useful for me)
Thanks to @ SJuan76, I managed to hit the target
select count(if(hr.estado = 4, 1, null)) as total, p.nombre_completo from pacientes p left join horas hr on p.id = hr.paciente_id where hr.medico_id = 1 group by p.id order by p.nombre_completo
PS: Sorry if I have done something wrong in the formulation of the question, I am new to this.