SQL query to find salary greater than average salary

Last Post

Posts: 4

Deepak Raj

Guest

Topic starter

May 23, 2022 10:36 am

(@Deepak Raj)

New Member

Joined: 1 year 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 1 year ago by SQL Admin

3 Replies

Posts: 10

SQL Admin

Admin

May 23, 2022 10:47 am

(@sql-admin)

Active Member

Joined: 3 years ago

Hello , Check the attached document for

SQL query to find salary greater than average salary

Here I have shared the two methods.

SQL-employee-grater-than-the-avg-salary.PNG

This post was modified 1 year ago by SQL Admin

Posts: 4

Mahitha

Guest

Topic starter

May 23, 2022 10:50 am

(@Mahitha)

New Member

Joined: 1 year ago

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

Posts: 4

Pooja Pandy

Guest

Topic starter

May 23, 2022 10:54 am

(@Pooja Pandy)

New Member

Joined: 1 year 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;