Thread: simple query

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    1
    Rep Power
    0

    simple query


    hi there,

    I'm not sure if this is the correct place for my query -

    i'm an sql newbie and my query is quite basic

    i have to return the job types whose minimum salary is greater then the average salary for salesman.

    i know this is a subquery within a subquery but i cant get it to work. i split it into 2 parts just to get the separate results but when put part2 as a subquery of part1 it doesnt work
    part 1
    Code:
    select min(sal), job
    from emp
    group by job;
    part 2
    Code:
    select avg(sal)
    from emp
    where job = 'SALESMAN';

    Code:
    select job
    from emp
    where sal > (select min(sal) from emp
    where sal > (select avg(sal) from emp
    where job = 'SALESMAN'));
    Please forgive my ignorance and if this isnt the correct forum for this query could someone redirect me to the correct one. Thanks!!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    I think the following does it:

    select min(sal), job
    from emp
    group by job
    having min(sal) > ( select avg(sal)
    from emp
    where job = 'SALESMAN')

    Regards,
    Dan

IMN logo majestic logo threadwatch logo seochat tools logo