Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    56

    Unanswered: HELP!! - SQL Statement does not work!

    select d.dname, count(s.staffid) scount
    from tstaff s, tdept d
    where s.deptid = d.deptid
    group by d.dname
    having scount > (select avg(count(s.staffid))
    from tstaff s
    group by s.staffid)
    ;

    can anyone tell me why the above statement does not run. I am getting the following errors:

    ERROR at line 5:
    ORA-00904: invalid column name

    I am trying to get the name of the department and the number of staff who have a higher than average number of staff assigned to that department.... any suggestions!?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    scount is a column alias so you might want to put the actual expression into the HAVING clause

    however, there's still a problem

    the subquery in the HAVING clause is not scalar, i.e. it can return more than one value


    rudy

  3. #3
    Join Date
    Jan 2003
    Posts
    56
    Originally posted by r937
    scount is a column alias so you might want to put the actual expression into the HAVING clause

    however, there's still a problem

    the subquery in the HAVING clause is not scalar, i.e. it can return more than one value


    rudy
    Thanks! I will try that.

  4. #4
    Join Date
    Jan 2003
    Posts
    56
    Originally posted by r937
    scount is a column alias so you might want to put the actual expression into the HAVING clause

    however, there's still a problem

    the subquery in the HAVING clause is not scalar, i.e. it can return more than one value


    rudy
    Hi,

    I tried what you suggested but I am still getting problems. Is there an easier way of comparing the number of staff in each department to the overall average across all departments?!

    C.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the error probably wasn't the alias, then -- like i said, your subquery wasn't scalar
    Code:
    select d.dname, count(s.staffid) scount
    from tstaff s, tdept d
    where s.deptid = d.deptid
    group by d.dname
    having count(s.staffid) > 
      ( select avg(deptcount)
          from ( select d.deptid, count(*) as deptcount
                          from tstaff s, tdept d
                        where s.deptid = d.deptid
                      group by d.deptid ) as deptcounts
      )
    caution: untested
    Last edited by r937; 04-30-03 at 11:16.

  6. #6
    Join Date
    Jan 2003
    Posts
    56
    Originally posted by r937
    the error probably wasn't the alias, then -- like i said, your subquery wasn't scalar
    Code:
    select d.dname, count(s.staffid) scount
    from tstaff s, tdept d
    where s.deptid = d.deptid
    group by d.dname
    having count(s.staffid) > 
      ( select avg(deptcount)
          from ( select d.deptid, count(*) as deptcount
                          from tstaff s, tdept d
                        where s.deptid = d.deptid
                      group by d.deptid ) as deptcounts
      )
    caution: untested

    cheers! I will give it a go - thanks a million.

Posting Permissions

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