select dept names who have more than 2 employees whose salary is greater than 1000

Question

How would do the following in SQL

"select dept names who have more than 2 employees whose salary is greater than 1000" ?

DeptId DeptName
------ --------
1          one
2          two
3        three

EmpId DeptId Salary
----- ------ ------
121      1    2000
122      1    2000
123      1    5000
124      1    4000
131      2    2000
132      2    6000
133      2    1000
134      2    1000
125      3    1000
126      3   20000


RESULT: one

Why does employee #121 have three salaries - one in dept 1 and 2 in dept 3? — Larry Lustig, Jan 10 '11 at 14:48

score 14 · Accepted Answer · answered Jan 10 '11 at 11:17

14

How about something like this?

SELECT D.DeptName FROM
Department D WHERE (SELECT COUNT(*) 
                    FROM Employee E 
                    WHERE E.DeptID = D.DeptID AND
                            E.Salary > 1000) > 2

answered Jan 10 '11 at 11:17

John Petrak

2,898
20
31

This worked as expected and to me it seems to be the best answer. Please let me know if anyone differs. – Anshul Jan 11 '11 at 10:13

score 5 · Answer 2 · edited Oct 20 '12 at 20:18

5

SELECT DEPTNAME
FROM(SELECT D.DEPTNAME,COUNT(EMPID) AS TOTEMP
     FROM DEPT AS D,EMPLOYEE AS E
     WHERE D.DEPTID=E.DEPTID AND SALARY>1000
     GROUP BY D.DEPTID
     )
WHERE TOTEMP>2;

edited Oct 20 '12 at 20:18

Bo Persson

90,663
31
146
203

answered Oct 20 '12 at 19:25

D23

51
1
1

score 3 · Answer 3 · edited Jan 11 '11 at 12:24

3

select min(DEPARTMENT.DeptName) as deptname 
from DEPARTMENT
inner join employee on
DEPARTMENT.DeptId = employee.DeptId
where Salary > 1000
group by (EmpId) having count(EmpId) > =2

edited Jan 11 '11 at 12:24

abatishchev

98,240
88
296
433

answered Jan 10 '11 at 11:25

Pankaj Agarwal

11,191
12
43
59

Pankaj this didn't work. I would be thankful if you post the corrected solution. I would learn. – Anshul Jan 11 '11 at 10:13

score 2 · Answer 4 · answered Aug 26 '14 at 09:45

2

select D.DeptName from [Department] D where D.DeptID in 
( 
    select E.DeptId from [Employee] E
    where E.Salary > 1000
    group by E.DeptId
    having count(*) > 2
)

answered Aug 26 '14 at 09:45

Pankaj Singh

71
1
3

score 2 · Answer 5 · answered May 21 '11 at 09:03

2

hope this helps

select DeptName from DEPARTMENT inner join EMPLOYEE using (DeptId) where Salary>1000 group by DeptName having count(*)>2

answered May 21 '11 at 09:03

user763822

21
1

score 1 · Answer 6 · answered Mar 15 '16 at 07:09

1

1:list name of all employee who earn more than RS.100000 in a year.

2:give the name of employee who earn heads the department where employee with employee I.D

answered Mar 15 '16 at 07:09

UMAIR SHAH

11
1

score 1 · Answer 7 · edited Nov 19 '11 at 20:59

1

select deptname from dept_1
where exists
(
SELECT DeptId,COUNT(*)        
FROM emp_1  
where salary>1000
and emp_1.deptid=dept_1.deptid   
GROUP BY DeptId 
having count(*)>2)

edited Nov 19 '11 at 20:59

animuson

53,861
28
137
147

answered Sep 14 '11 at 06:52

Deepak Varshney

11
1

Welcome on SO, here, it is a good practice to explain why to use your solution and not just how. That will make your answer more valuable and help further reader to have a better understanding of how you do it. I also suggest that you have a look on our FAQ : http://stackoverflow.com/faq. – ForceMagic Nov 12 '12 at 01:36

onedaywhen · Answer 8 · 2019-05-03T10:04:55.217

0

My main advice would be to steer clear of the HAVING clause (see below):

WITH HighEarners AS
     ( SELECT EmpId, DeptId
         FROM EMPLOYEE
        WHERE Salary > 1000 ), 
     DeptmentHighEarnerTallies AS 
     ( SELECT DeptId, COUNT(*) AS HighEarnerTally
         FROM HighEarners
        GROUP 
           BY DeptId )
SELECT DeptName
  FROM DEPARTMENT NATURAL JOIN DeptmentHighEarnerTallies
 WHERE HighEarnerTally > 2;

The very early SQL implementations lacked derived tables and HAVING was a workaround for one of its most obvious drawbacks (how to select on the result of a set function from the SELECT clause). Once derived tables had become a thing, the need for HAVING went away. Sadly, HAVING itself didn't go away (and never will) because nothing is ever removed from standard SQL. There is no need to learn HAVING and I encourage fledgling coders to avoid using this historical hangover.

edited May 03 '19 at 10:04

answered Jan 10 '11 at 14:31

onedaywhen

55,269
12
100
138

1

On what do you base your advice to steer clear of "HAVING" - an operator that seems designed specifically to answer the OP's question in a clear and easy fashion? – Larry Lustig Jan 10 '11 at 14:47
1

That article contains a very idiosyncratic view of HAVING. The argument against it is pretty lame (that people have to learn the difference between operating on the candidate rows and the aggregated result set). Can you possibly argue that the solution you gave is superior Pankaj's? – Larry Lustig Jan 10 '11 at 15:30
@onedaywhrn - your solution worked but I am naive to understand how to replace the values and get it from the Tables. – Anshul Jan 11 '11 at 10:15
Overkill. Why to use? "Because you can"? – abatishchev Jan 11 '11 at 12:26

select dept names who have more than 2 employees whose salary is greater than 1000

8 Answers8