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