Rankin in in Mysql

Question

I’m trying to generate a ranking of absences for HR.

The record of absences is made in a separate table of the Employee’s registration. I need to bring the list of active employees and the amount of absences he has had in the company, only I want to order the employee who had more absences for what had less.

I made the SQL below, but brings only the employees who had missed:

select rh.NOME_FUNCIONARIO, COUNT(registro_faltas_atraso.CPF) as Total from RH 
JOIN registro_faltas_atraso
on  registro_faltas_atraso.CPF =rh.cpf
where 
rh.TIPO_CONTRATO !='Dispensado'
GROUP BY rh.NOME_FUNCIONARIO
ORDER BY desc total

Answer 1

SELECT      RH.NOME_FUNCIONARIO
        ,   SUM(CASE WHEN RFA.CPF IS NOT NULL THEN 1 ELSE 0 END) AS Total 
FROM        RH 
LEFT JOIN   registro_faltas_atraso RFA ON RFA.CPF = RH.cpf
WHERE       RH.TIPO_CONTRATO != 'Dispensado'
GROUP BY    RH.NOME_FUNCIONARIO
ORDER BY    Total DESC