Rankin in MySQL

Navigation
1

I'm trying to generate a ranking of faults for HR.

Fault logging is done in a separate table in the Employee registry. I need to bring the list of active employees and the amount of absences he has had in the company, but I want to order from the employee who had more absences for what he had less.

I did the SQL below, but it only brings the employees who have failed: 

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
    
asked by anonymous 31.08.2018 / 16:14

1 answer

0
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
    
31.08.2018 / 16:21
Registering new date in MySQL Calculate items in a table