How to discriminate elements from database that have many occurences

Question

I have a table called ProjectRessources with the following data:

+-----------+------------+---------+-----------+
| projectId | employeeId | nbHours | wageHours |
+-----------+------------+---------+-----------+
| 1         | 1876       | 500     | 65        |
| 1         | 4354       | 2000    | 31        |
| 2         | 2231       | 250     | 55        |
| 3         | 1212       | 3000    | 35        |
| 3         | 1876       | 2000    | 35        |
| 3         | 2231       | 500     | 65   

I must query the employees that have worked on more than one projects to obtain something like this:

+------------+----------------+
| employeeId | nbProjects |
+------------+----------------+
| 1876       | 2              |
| 2231       | 2              |
+------------+----------------+

I have tried

    SELECT DISTINCT employeeID,projectID
    FROM ProjectRessources
    WHERE COUNT(projectID) >1;

but I get an error 1111 on my use of COUNT

ERROR 1111 (HY000): Invalid use of group function

I have then attempted something like this

SELECT DISTINCT
employeeId ,
COUNT(projectId) as nbProjects
From ProjectRessources
WHERE nbProjects >1;

But then I obtain the following error

ERROR 1054 (42S22): Unknown column 'nbProjects' in 'where clause'

Please help, any feedback is appreciated. Please note that I am not allowed to use GROUP BY to make my query.

Answer 1

You can try joining the ProjectResources table with itself as follows:

select distinct p1.employeeID from
ProjectResources p1 join ProjectResources p2
    on p1.employeeID = p2.employeeID and
       p1.projectID <> p2.projectID

This just tries to see for each row if there is another row that has the same employeeID value but a different projectID value. We don't care if how many of them there are as long as there is at least one and that is why we select distinct so that the same employeeID does not appear more than once (without the distinct keyword, we would get one row for every project the employee worked on).

I used the table from your updated question to create an actual table at sqlfiddle.com. Next time you can (and should) do this yourself and post the link in your question.

See Demo

But this sounds suspiciously like homework.

If you must have the counts:

select distinct employeeID, count(*) OVER(PARTITION By employeeID) as nbProjects from (
   select p1.employeeID from
   ProjectResources p1 join ProjectResources p2
       on p1.employeeID = p2.employeeID and
          p1.projectID <> p2.projectID
  ) sq;

employeeID	nbProjects
1876	2
2231	2

---

View on DB Fiddle

Answer 2

You want having . . . and the right group by:

SELECT employeeID
FROM ProjectRessources
GROUP BY employeeId
HAVING COUNT(*) > 1;

If you want the list of projects, I would recommend GROUP_CONCAT():

SELECT employeeID, GROUP_CONCAT(projectID) as projectIDs
FROM ProjectRessources
GROUP BY employeeId
HAVING COUNT(*) > 1;

Answer 3

Without GROUP BY, you can do it with a correlated subquery:

SELECT *
FROM (
  SELECT DISTINCT pr.employeeId,
         (SELECT COUNT(*) FROM ProjectRessources pr2 WHERE pr2.employeeId = pr.employeeId) nbProjects 
  FROM ProjectRessources pr
) t
WHERE nbProjects > 1

If there is a case of duplicate projectIds for each employeeId change COUNT(*) with:

SELECT COUNT(DISTINCT projectId) FROM ...

If your version of MySql is 8.0+ and there are no duplicate projectIds for each employeeId, you can do it with COUNT(*) window function:

SELECT *
FROM (
  SELECT DISTINCT employeeId,
         COUNT(*) OVER (PARTITION BY employeeId) nbProjects 
  FROM ProjectRessources pr
) t
WHERE nbProjects > 1

See the demo.
Results:

> employeeId | nbProjects
> ---------: | ---------:
>       1876 |          2
>       2231 |          2