CODEWITHSUNDEEP
mysqlsql
Modaresi
Modaresi

Reputation: 233

Count with condition-mysql

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.

Upvotes: 1

Views: 381

Answers (3)

Shafeeque
Shafeeque

Reputation: 2069

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

Upvotes: 2

Clart Tent
Clart Tent

Reputation: 1309

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

Upvotes: 2

Barmar
Barmar

Reputation: 780818

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.

Upvotes: 0

Related Questions