Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    2

    Unanswered: a querie urgent question

    hello guys , hope you are having a nice day
    i got a problem solving a question on my assignment , hope to find some1 that can help me on it .

    the question is :
    For each department-- if its average salary is less than the average salary of all departments-- display its number, name and number of its employees.

    that was my sql code but i ended up with an error :

    SELECT Dnum, Dname, count(e.SSN) AS [Number of employees]
    FROM departments AS d, employee AS e
    WHERE d.Dnum=e.dno
    GROUP BY dnum, dname
    HAVING (select avg(salary) from employee group by dno) < avg(salary);

    any clue ?

    thanks for your time , goodluck !

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you were doing fine up until the HAVING clause

    (not quite up to best practice, though, because of the "naked" columns in the query -- you have qualified some of the columns, e.g. e.SSN and d.Dnum, but in any query involving more than one table, you should qualify ~all~ columns)

    on the right side of the less than sign, you have AVG(salary) -- where does that come from?

    and in the HAVING subquery, what's the purpose of the GROUP BY?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2010
    Posts
    2
    i need to get info for departments that the avr of its employee's salaries less than the the total avr of all salaries for all employees , that why i tried to group the subQ in the having clause .

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by engluver View Post
    that why i tried to group the subQ in the having clause .
    you realize that a GROUP BY clause produces one row per grouping column, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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