CODEWITHSUNDEEP
mysqlsql-server
pavithra sundaram
pavithra sundaram

Reputation: 31

Display the greater than the average salary by deptid wise

I have two tables one table having deptid, empid, salary and another table having empid, name 

Table1
+--------+-------+--------+
| deptid | empid | salary |
+--------+-------+--------+
| coe    |     1 |  19000 |
| coe    |     2 |  13000 |
| coe    |     3 |  14000 |
| igbm   |     4 |  15000 |
| igbm   |     5 |  21000 |
| igbm   |     6 |  31000 |
+--------+-------+--------+

Table2
+-------+------+
| empid | name |
+-------+------+
|     1 | a    |
|     2 | b    |
|     3 | c    |
|     4 | d    |
|     5 | e    |
|     6 | f    |
+-------+------+

average salary
+--------+-------------+
| deptid | avg(salary) |
+--------+-------------+
| coe    |  15333.3333 |
| igbm   |  22333.3333 |
+--------+-------------+

I need who got more than avg(salary) in deptid wise

| deptid | empid | salary | name     |
+--------+-------+--------+----------+
| coe    |     1 |  19000 | a        |
| igbm   |     6 |  31000 | f        |


select a.deptid, a.empid, a.salary, b.name from Table1 a, Table2 b where a.empid= b.empid and salary>(select avg(salary) from Table1) group by deptid ;


I used this query it's not working 

+--------+-------+--------+----------+
| deptid | empid | salary | name     |
+--------+-------+--------+----------+
| coe    |     1 |  19000 | a        |
| igbm   |     5 |  21000 | e        |
+--------+-------+--------+----------+


select a.deptid, a.empid, a.salary, b.name from Table1 a, Table 2 where a.empid= b.empid and salary>(select avg(salary) from Table1 order  by deptid);

+--------+-------+--------+----------+
| deptid | empid | salary | name     |
+--------+-------+--------+----------+
| coe    |     1 |  19000 | a         |
| igbm   |     5 |  21000 | e         |
| igbm   |     6 |  31000 | f         |
+--------+-------+--------+----------+

I use this query, this is take overall average not deptid wise.

Upvotes: 2

Views: 91

Answers (1)

James Z
James Z

Reputation: 12317

You just need to fetch the average of the department, not all:

select a.deptid, a.empid, a.salary, b.name 
from Table1 a, 
Table2 b
where 
  a.empid= b.empid and 
  a.salary>(
    select avg(salary) 
    from Table1 c 
    where c.deptid = a.deptid
  )

Example in SQL Fiddle

Upvotes: 3

Related Questions