Forum

SQL query to find s...
 
Share:
Notifications
Clear all

SQL query to find salary greater than average salary

4 Posts
2 Users
0 Likes
1,344 Views
Deepak Raj
Posts: 4
 Deepak Raj
Guest
Topic starter
(@Deepak Raj)
New Member
Joined: 5 months 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

3 Replies
SQL Admin
Posts: 8
Admin
(@sql-admin)
Active Member
Joined: 2 years ago

Hello , Check the attached document for

SQL query to find salary greater than average salary

Here I have shared the two methods. 

Reply
Mahitha
Posts: 4
 Mahitha
Guest
Topic starter
(@Mahitha)
New Member
Joined: 5 months ago

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

Reply
Pooja Pandy
Posts: 4
 Pooja Pandy
Guest
Topic starter
(@Pooja Pandy)
New Member
Joined: 5 months 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
Share: