Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Posts
    18

    Question Unanswered: How do i get an Average and select the lowest value?

    +----------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | Doc | varchar(5) | NO | PRI | NULL | |
    | Name | varchar(20)| NO | | NULL | |
    | Salary | varchar(7) | NO | | NULL | |
    | City | varchar(10)| NO | | NULL | |
    | Boss | varchar(5) | YES| | NULL | |
    +----------+-------------+------+-----+---------+-------+
    In this table theres Doc (which is document and the primary key) of the employee, the name of the employee, the salary of the employee, the city in which the employee is at, and the boss which is the Doc.
    so i put some info in and it looks like this:
    +-------+----------+---------+----------+-------+
    | Doc | Name | Salary | City | Boss |
    +-------+----------+---------+----------+-------+
    | 10305 | Joey | 700 | NYC | NULL |
    | 10244 | John | 900 | Boston | 10305 |
    | 35357 | Mary | 600 | Boston | 10305 |
    | 90012 | Becky | 650 | Miami | 35357 |
    | 10235 | Jose | 400 | LA | 35357 |
    | 95632 | Anthony | 900 | Miami | 35357 |
    | 12345 | Mateo | 500 | NYC | 10305 |
    +-------+----------+---------+------------+-------+

    Joey is the boss of:
    -John
    -Mary
    -Mateo
    Mary is the boss
    -becky
    -Jose
    -anthony
    I have to average the salary of the people that work for the same boss, that means and average of John, mary and mateo since they work for joey and another of becky, jose and anthony since they are mary's employee.

    Ok now when i get the average of each work group, now i have to select the persons in each team that earns lower than the average salary of that work group. How do i do this?
    I tried:

    SELECT T2.min(salary), T1.min(salary) from employees T1 INNER JOIN employees T2 ON T1.Doc = T2.Boss WHERE avg(t2.salary) AND avg(t1.salary);

    but no luck...
    THANKSSSS
    ..::Antonioj1015::..

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Try something like(written on fly, so test):
    Select salary,doc,boss
    from employees t1
    ,(select avg(t2.salary) as avg_sal, boss
    from employees t2) as t2
    where t1.boss = t2.boss
    and t1.salary < t2.avg_sal

    Dave

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    another shot before I move on:
    Select cols...
    from t1
    where salary < (select avg(salary)
    from t2
    where t1.boss = t2.boss)
    Though, with this one you wouldn't be able to select the avg salary as well.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •