mysql - sql query for a certain condition -

i have table like:

empid empsalary empdept 1   45000   2   40000   3   50000   sales 4   60000   sales 5   75000   6   80000   7   25000   ops 8   30000   ops 9   55000   marketing 10  60000   marketing 

i have write query as:

select empid empsalary  > avg(empsalary) each  empdept 

kindly help.

try this:

select e.empid, e.empsalary, e.empdept   employee e  inner join (select e1.empdept, avg(e1.empsalary) empsalary              employee e1              group e1.empdept            ) on e.empdept = a.empdept , e.empsalary > a.empsalary; 

edit

select e.empdept, count(distinct e.empid) noofemployees employee e  inner join (select avg(e1.empsalary) empsalary employee e1) on e.empsalary > a.empsalary group e.empdept;