Select within select sorted by the second mysql query

Question

I got a problem, I don’t know if I’m doing it right, but come on.

I have a table that lists some employees ( tblTecnicos ).

I have another table , which has the records of services that these technicians performed (tblServicos) , in this table , has a field that calls "type Rvico", in it , has the id of some service that the technician held.

Ex : Services :

1 - maintenance 2 - Formwork

In the table tblServicos

• idTechnique typeService
• 5 2
• 5 1
• 2 2
• 1 1
• 5 2
• 2 1

Type , if I put to list the technicians , list :

• idTecnico 1
• idTecnico 2
• idTecnico 5

etc.

I would like to make a select by listing all the techniques, ordered by the largest amount of services they have performed.

In the example , the technician with ID 1 made 1 service , the technician with id 2 made 2 services and the technician with ID 5 made 3 services.

At the time of performing the select , I would like it to be ordered by the largest number of services they have performed.

• idTecnico 5
• idTecnico 2
• idTecnico 1

I tried that way, but it didn’t work :

SELECT * FROM tblTecnicos where (select count(tipoServico) from tblServicos where tipoServico > 0 order by(tipoServico)DESC) 

Does anyone know if they can do it?

EDITION

I managed to solve part of the problem with the code below , but it is only returning 1 result. What is wrong ?

SELECT tblTecnicos.nome, count(tblServicos.tipoServico) 
FROM tblTecnicos 
    INNER JOIN tblServicos ON tblServicos.tipoServico > 0
AND tblTecnicos.id = tblServicos.idTecnico 
ORDER BY tblServicos.tipoServico DESC

Answer 1

The problem is you have to do the count of tipoServico in the ORDER BY:

SELECT * 
FROM tblTecnicos 
WHERE tipoServico > 0
ORDER BY COUNT(tipoServico) DESC

If you wanted to present the result by presenting (only the idTecnico), you have to do one more GROUP BY:

SELECT idTecnico 
FROM tblTecnicos 
WHERE tipoServico > 0
GROUP BY idTecnico 
ORDER BY COUNT(tipoServico) DESC

Answer 2

After studying a little about the JOIN MYSQL, I managed to solve my problem .

It follows as it turned out:

SELECT a.id,a.nome,count(b.tipoServico) 
FROM tblTecnicos AS a 
INNER JOIN tblServico AS b 
ON (a.id = b.idTecnico) 
GROUP BY a.nome 
ORDER BY COUNT(b.tipoServico) DESC 
LIMIT 12