HELP!! - SQL Statement does not work!

coolowen · #1 (**permalink**) 04-30-03, 08:28

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 · #2 (**permalink**) 04-30-03, 08:38

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 · #3 (**permalink**) 04-30-03, 08:43

Quote:

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 · #4 (**permalink**) 04-30-03, 09:52

Quote:

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 · #5 (**permalink**) 04-30-03, 10:04

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

coolowen · #6 (**permalink**) 04-30-03, 10:24

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech