Return amount of dependents per employee

Question

I need to select the Plate, the Employee Name and the Number of Dependents of each Employee, but the result of this query is showing the amount of dependents only for the first employee, and I need the quantities for all employees.

Note: If you remove the clause WHERE, query shows the amount of all dependent table dependent only on the first employee.

Tables: COLLABORATOR AND DEPENDENT

SELECT C.CHAPA, C.NOMECOLABORADOR,
    COUNT(D.NOMEDEP) AS QNT_DEP 
    FROM COLABORADOR C, DEPENDENTE D
    WHERE D.CHAPA=C.CHAPA;

Answer 1

The way you’re doing the query, the function COUNT() will count all returned records.

However, you want to count dependents for each employee, so you need to group the results with the clause GROUP BY. Thus the function COUNT() will count dependents within that defined group, rather than all records returned.

Would look like this:

SELECT C.CHAPA, C.NOMECOLABORADOR, COUNT(D.NOMEDEP) AS QNT_DEP 
FROM COLABORADOR C, DEPENDENTE D
WHERE D.CHAPA = C.CHAPA
GROUP BY C.CHAPA, C.NOMECOLABORADOR;

This is the form dictated by the SQL92 standard (revision of the SQL language), which says that the fields that appear in the clause SELECT should also appear in the clause GROUP BY, if it exists. But Mysql can follow the SQL99 standard, depending on how it is configured (documentation), and then you could use just the field CHAPA to make the grouping:

SELECT C.CHAPA, C.NOMECOLABORADOR, COUNT(D.NOMEDEP) AS QNT_DEP 
FROM COLABORADOR C, DEPENDENTE D
WHERE D.CHAPA = C.CHAPA
GROUP BY C.CHAPA;