Post reply

Aggregate function

  • September 25, 2007 at 8:57 am

    #118354

    Hi All,

    Please tell me how to find employee's salary is less than the average salary of his department

    Table name: edtl

    empid empname sal depid

    ----------- ------- ----------- -----------

    1 gana 200 10

    2 kishore 300 50

    3 karthi 500 30

    4 Venkat 500 30

    5 Jai 300 20

    6 Mani 1300 20

    7 John 100 10

    8 Ravi 1000 10

    9 Ram 900 50

    Regards,

    Ganpat

  • September 25, 2007 at 9:04 am

    #736060

    derived table is the name of the game.

    select e.empid, e.empname, e.salary, da.avgsalary, e.deptid

    from

    edtl inner join

    (select deptid, avg(salary) as avgsalary from edtl group by deptid) da

    on e.deptid=da.deptid

    where e.salary<da.avgsalary

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • September 25, 2007 at 9:08 am

    #736062

    Probably the best way would be to calculate average salary for each department, and then compare it with the salary of a given employee. Use of a derived table would allow you to find all employees that have salary lower than average of their department.

    This looks a little bit like homework... but even if it isn't one - could you please tell us what you tried and what problems you encountered? What precisely do you need help with? How to calculate average? Or how to compare it with employee salary?

  • September 25, 2007 at 11:25 am

    #736106

    SELECT empname

    FROM edtl

    GROUP BY empname, sal

    HAVING sal < (SELECT AVG(sal) FROM edtl)

  • September 26, 2007 at 12:57 am

    #736257

    David,

    your solurion does not take into account the requirement "lower than average of their department".

    SELECT e.empname

    FROM edtl e

    GROUP BY e.empname, e.sal

    HAVING e.sal < (SELECT AVG(sal) FROM edtl WHERE depid = e.depid)

    would do the trick, but a corellated subquery has worse performance than if you use derived table as posted above.

  • September 26, 2007 at 3:42 am

    #736285

    Hi Experts

    Thanks for your reply... 🙂

    Ganpat

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply