SQL problem

Question

I need to create an SQL that shows the registration numbers of employees of a table, their names and the number of people dependent on employees who have more than one dependent, ordering the result in descending order by the number of dependents.

I made the following SQL:

SELECT c.numCad,
c.nomFun,
(SELECT COUNT (d.numCad) FROM dependentes d AS contDep WHERE d.numCad = c.numCad 
HAVING contDep > 1) qtDep
FROM colaboradores c
ORDER BY qtDep DESC;

Because I feel that these lines present an error:

(SELECT COUNT (d.numCad) FROM dependentes d AS contDep WHERE d.numCad = c.numCad HAVING contDep > 1) qtDep

And I’m out of ideas of what to do about it.

The problem requires numCad to be Primary key table colaboradores and Foreign key table dependentes.

Answer 1

Experiment as follows:

SELECT      c.numCad
        ,   c.nomFun
        ,   qtDep.dependentes
FROM        colaboradores c
LEFT JOIN   (
                SELECT      numCad
                        ,   COUNT(1) AS dependentes
                FROM        dependentes
                GROUP BY    numCad
                HAVING      COUNT(1) > 1
            ) qtDep ON qtDep.numCad = c.numCad
ORDER BY    qtDep.dependentes