[coolowen]

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!?

[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

[coolowen]

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.

[coolowen]

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.

[r937]

the error probably wasn't the alias, then -- like i said, your subquery wasn't scalar

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

[coolowen]

Originally posted by r937
the error probably wasn't the alias, then -- like i said, your subquery wasn't scalar

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.