CODEWITHSUNDEEP
sqlselectequals
user3691006
user3691006

Reputation: 185

Select all the people that have the same salary

I have problems finding a solution to an SQL query. This is probably a very obvious beginner question but I can't seem to get the results that I'm after. I have a table that looks something like the following:

|Name |Station|Salary|
|Bob  |1      |2000  |
|Steve|2      |1750  |
|Mark |3      |2050  |
|Lisa |4      |2200  |
|Hans |5      |2000  |

I would like to select the names of the people in this table that have the same salary. The result should of course be Bob and Hans.

Upvotes: 10

Views: 89580

Answers (15)

ganesh kavhar
ganesh kavhar

Reputation: 33

SUPPOSE WE HAVE TABLE NAMELY AS EMP3 WHICH ONTEIN COLUMNS AS FIRST_NAME, LAST_NAME, SALRY, DEPARTMENT_ID, COMMISSION, ETC.

NOW WE HAVE TO SELECT RECORDS OF EMPLOYEES WHO HAVE THE SAME SALARY.

LET'S GET OUTPUT WITH THE HELP OF SELF JOIN.

HERE WE ARE JOINING THE SAME TABLE WITH ITSELF TO GET RECORDS WHO HAVE THE SAME SALARY.

SELECT E1.FIRST_NAME AS FN,E1.SALARY FROM EMPLOYEES E1 INNER JOIN EMPLOYEES E2 ON E1.SALARY=E2.SALARY;

Upvotes: 0

guzel6031
guzel6031

Reputation: 11

Treating same table as a separate two table.

SELECT DISTINCT T.username, T.salary
FROM account T, account T
WHERE T.salary = T.salary
AND T.username <> T.username
ORDER BY salary;

Upvotes: 0

Ankit Wanwe
Ankit Wanwe

Reputation: 1

First Solution :

Select e1.name from employee e1 inner join employee e2 on e1.salary=e2.salary AND e1.name <> e2.name

Second easy solution:

Select name from employee where salary In (Select salary from employee group by salary having count(*)>1)

Upvotes: 0

Rahul Baghaniya
Rahul Baghaniya

Reputation: 321

lets say table name is: employee

select distinct e.name, e.salary from employee e, employee e1 where e.salary = e1.salary and e.name != e1.name;

Upvotes: 0

Curious_Mind
Curious_Mind

Reputation: 1

For MySQL:

Select * from worker where salary in (select salary from worker group by salary having count(1)>1);

Upvotes: 0

Abhijit K
Abhijit K

Reputation: 1

Try this:

select COUNT(*) as NumberOfPerson,salary from tblEmployee group by salary having COUNT(*)>1

Upvotes: 0

mka
mka

Reputation: 21

TRY THIS:

SELECT E1.Name, E1.Salary
FROM Employee E1, Employee E2
WHERE E1.Salary = E2.Salary
AND E1.Name <> E2.Name

http://sqlfiddle.com/#!2/1e34b

Upvotes: 2

ngrashia
ngrashia

Reputation: 9904

Use below Query:

with datatab as
(
 select 'Bob' Name,   1 Station, 2000  Salary from dual union
 select 'Steve' Name,   2 Station, 1750  Salary from dual union
 select 'Mark' Name,   3 Station, 2050  Salary from dual union
 select 'Lisa' Name,   4 Station, 2200  Salary from dual union
 select 'Hans' Name,   5 Station, 2000  Salary from dual union
 select 'Test' Name,   6 Station, 1750  Salary from dual
)

SELECT NAME, sTATION, SALARY  FROM DATATAB
WHERE SALARY IN 
(
   SELECT Salary
   FROM datatab
   GROUP BY Salary
   HAVING COUNT(1) > 1
);

As someone suggested in Edits, The Query would be:

SELECT NAME, sTATION, SALARY  FROM TABLE_NAME
WHERE SALARY IN 
(
   SELECT Salary
   FROM TABLE_NAME
   GROUP BY Salary
   HAVING COUNT(1) > 1
)

Upvotes: 1

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186803

Use aliases when you have to put one and the same table two or more times:

select t1.Name,
       t2.Name,
       t1.Salary 
  from MyTable t1,
       MyTable t2
 where t1.Station > t2.Station and -- if Station is id 
       -- t1.Name > t2.Name -- if Name is in fact an id
       t1.Salary = t2.Salary

Upvotes: 0

Mark Giaconia
Mark Giaconia

Reputation: 3953

Select a.name, a.salary from ( Select count(salary) as cnt, salary from staff group by salary having count(salary) > 1

)as X Inner join staff a on a.Salary=x.salary

order by a.salary:

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Most databases support window functions. The simple method to do this is to count the number of people that have a given salary. Then choose those people. Here is an easy method:

select t.*
from (select t.*, count(*) over (partition by salary) as salarycnt
      from table t
     ) t
where salarycnt > 1
order by salary;

Upvotes: 2

StuartLC
StuartLC

Reputation: 107317

If you determine the salary bands which have more than one employee, you can then join back to it as a derived table:

SELECT e.Name, e.Station 
FROM Employee e
INNER JOIN
(
   SELECT Salary
   FROM Employee
   GROUP BY Salary
   HAVING COUNT(*) > 1
) grp ON e.Salary = grp.Salary;

SqlFiddle here

Upvotes: 4

Leonardo Pinheiro
Leonardo Pinheiro

Reputation: 17

Do you want the quantity of people with the same salary.

SELECT count(*) FROM table GROUP BY salary

Upvotes: -1

qxg
qxg

Reputation: 7036

SELECT Name
FROM table1 
WHERE Salary IN (
    SELECT Salary
    FROM table1
    GROUP BY Salary
    HAVING COUNT(*) > 1
)

Upvotes: 9

ydaetskcoR
ydaetskcoR

Reputation: 56937

If you join the table against itself on Salary but where the names are separate then this should give you any matching salaried people:

SELECT s1.Name, s1.Station, s1.Salary
FROM Staff s1
INNER JOIN Staff s2 ON s1.Salary = s2.Salary AND s1.Name <> s2.Name

Here's a SQLFiddle to show it in action

Upvotes: 13

Related Questions