Forum

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

SQL query to find salary greater than average salary


Deepak Raj
Posts: 4
 Deepak Raj
Topic starter
(@Deepak Raj)
Joined: 1 month 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)
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


Deepak Raj
Posts: 4
 Deepak Raj
Topic starter
(@Deepak Raj)
Joined: 1 month ago

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

Reply
Deepak Raj
Posts: 4
 Deepak Raj
Topic starter
(@Deepak Raj)
Joined: 1 month 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: