Forum

Forums
SQL
Interview Questions...
SQL query to find s...
 
Share:
Notifications
Clear all

SQL query to find salary greater than average salary

 
    Last Post
RSS

Posts: 4
 Deepak Raj
Guest
Topic starter
May 23, 2022 10:36 am
(@Deepak Raj)
New Member
Joined: 3 years ago

How to write an SQL query to find salary greater than average salary.

Here is the Answer. 

mysql> select * from MyTable;
+------+------------+------+
| id   | d2         | Ref  |
+------+------------+------+
|  500 | 2010-12-22 |    3 |
|  500 | 2011-12-22 |    2 |
|  500 | 2012-12-22 |    1 |
|  700 | 2011-12-22 |    1 |
+------+------------+------+
4 rows in set (0.00 sec)

mysql> select * from MyTable  having Ref > avg(Ref);
+------+------------+------+
| id   | d2         | Ref  |
+------+------------+------+
|  500 | 2010-12-22 |    3 |
+------+------------+------+
1 row in set (0.00 sec)

mysql> select * from MyTable  having Ref > (select avg(Ref) from MyTable);
+------+------------+------+
| id   | d2         | Ref  |
+------+------------+------+
|  500 | 2010-12-22 |    3 |
|  500 | 2011-12-22 |    2 |
+------+------------+------+
2 rows in set (0.02 sec)

mysql> select * from MyTable  where Ref > (select avg(Ref) from MyTable);
+------+------------+------+
| id   | d2         | Ref  |
+------+------------+------+
|  500 | 2010-12-22 |    3 |
|  500 | 2011-12-22 |    2 |
+------+------------+------+
2 rows in set (0.00 sec)

mysql> select *,avg(Ref) from MyTable  having Ref > avg(Ref);
+------+------------+------+----------+
| id   | d2         | Ref  | avg(Ref) |
+------+------------+------+----------+
|  500 | 2010-12-22 |    3 |   1.7500 |
+------+------------+------+----------+
1 row in set (0.00 sec)
Share
Improve this answer
Follow
answered May 23, 2022 at 17:26
This topic was modified 3 years ago by SQL Admin
3 Replies
Posts: 97
 SQL Admin
Admin
May 23, 2022 10:47 am
(@sql-admin)
Estimable Member
Joined: 5 years ago

Hello , Check the attached document for

SQL query to find salary greater than average salary

Here I have shared the two methods. 

This post was modified 3 years ago by SQL Admin
Reply
Posts: 4
 Mahitha
Guest
Topic starter
May 23, 2022 10:50 am
(@Mahitha)
New Member
Joined: 3 years ago

Thanks for your help. I was exactly looking for the same. The methods you have provided helped me a lot. 

Reply
Posts: 4
 Pooja Pandy
Guest
Topic starter
May 23, 2022 10:54 am
(@Pooja Pandy)
New Member
Joined: 3 years ago

Here is another approach to find the employees who are earning more salary than the avg salary from the employee table. 

SELECT empno, ename, deptnum
FROM emp e
WHERE sal > (SELECT AVG(sal)
FROM emp e1
WHERE e.deptnum = e1.deptnum
GROUP BY deptnum)
ORDER BY deptnum;
Reply

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: