CODEWITHSUNDEEP
mysqlsql
Modaresi
Modaresi

Reputation: 233

Having count(*) MySQL

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

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

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

Related Questions