SELECT pnumber, pname, COUNT(*)
FROM project
INNER JOIN works_on ON pno=pnumber
INNER JOIN department ON dnumber=dnum
GROUP BY pnumber
Right now, mysql will return the project number and name, along with the number of employees who are working on it. What I want to do is that mysql only count employees from department 5. In other words there are people from different departments working on projects, but I want only the ones from department 5 to be counted and not all.
If Barmar's and Shafeeq's suggestion of using a WHERE clause won't work for you (for example, if you need to include all departments in your result, but only count people from department 5) you can use this:
SUM(IF(dnum=5, 1, 0)) AS CountFromDepartment5
So we'd have:
SELECT pnumber, pname, COUNT(*) AS TotalCount, SUM(IF(dnum=5, 1, 0)) AS CountFromDepartment5
FROM project
INNER JOIN works_on ON pno=pnumber
INNER JOIN department ON dnumber=dnum
GROUP BY pnumber
You can use WHERE
SELECT pnumber, pname, COUNT(*)
FROM project
JOIN works_on ON pno = pnumber
JOIN department ON dnumber=dnum
WHERE dnumber = 5
GROUP BY pnumber
EDIT
SELECT pnumber, pname, COUNT(*),SUM(IF(dnumber=5, 1, 0)) AS count5
FROM project
JOIN works_on ON pno = pnumber
JOIN department ON dnumber=dnum
GROUP BY pnumber
Use a WHERE clause that specifies the department you want.
SELECT pnumber, pname, COUNT(*)
FROM project
INNER JOIN works_on ON pno=pnumber
WHERE dnumber = 5
GROUP BY pnumber
You don't need to join with department, since the department number also exists as a foreign key in the works_on table.
WHEREclauses are for?WHEREclause. It would help if you showed the schema. Why do projects have department numbers? People are in departments, projects aren't.