Reputation: 233
SELECT
dnum
, SUM(IF(salary>20000, 1, 0)) AS Employee
FROM project
INNER JOIN works_on
ON pno=pnumber
INNER JOIN employee
ON dno=dnum
GROUP BY dnum HAVING COUNT(*) >2
I first want to determine whether each department has more than 2 employees or not, if true then get the department number and count of the employees. But only those who make higher than 20,000.
The way I have done it, it shows correct results for one of the departments, but for others it shows a complete wrong count. For example, for department 4 it shows 18, but the correct count should be only 6.
Upvotes: 1
Views: 88
Reputation: 79979
Use WHERE salary > 20000
instead, there is no need to use CASE
for it:
SELECT
dnum,
COUNT(*) AS Employee
FROM project
INNER JOIN works_on ON pno = pnumber
INNER JOIN employee ON dno = dnum
WHERE salary > 20000
GROUP BY dnum
HAVING COUNT(*) >2;
You might also need to use COUNT (DISTINCT EmployeeNumber)
instead if there is duplicate numbers.
Upvotes: 1