Re: how to make this query ?

It works

select a.deptno, a.dname, count(b.ename) from dept a, emp b
where b.deptno = a.deptno
group by a.deptno,

               a.dname having count(b.ename) = (select max(depcnt)
                                                                     from

from emp

group by deptno )

                                                                     )

"Bhooshan Prabhu" <bhooshan.s.prabhu_at_orbitech.co.in> wrote in message news:16584988.0205082048.185210a4_at_posting.google.com...
> Hello,
>
> Could you please try the following SQL.
>
> select a.deptno, a.deptname, count(b.name)
> from dept a, emp b
> where b.deptno = a.deptno
> group by a.deptno, a.deptname
> having count(b.name) =
> (select max(empcnt)
> from (select deptno, count(name) as empcnt from emp group by
 deptno));
>
> Regards
> Bhooshan
>
> -------------------------------------------------------------------------
>
> pratik <pratikk_at_iprimus.com.au> wrote in message
 news:<Xns9207E21282649pratikkhasnabis_at_203.134.67.67>...
> > I have two tables for employees and department.
> > 1. EMP
> > columns are NAME (varchar) and DEPTNO (number)
> > 2. DEPT
> > columns are DEPTNO (number) DEPTNAME (varchar)
> >
> > the task is to find the department with largest no of employees with a
> > single query.
> > i.e to show the DEPTNAME , DEPTNO and no. of employees.
> >
> > I am not a student , this was resulting from a discussion with a
 colleague.
> > I have very basic knnowldege of SQL. The constraint is not to use
 temporary
> > tables , views , PL/SQL and other oracle specific feature. ANSI SQL
> > solution is most preferred.
> > I have only mySQL at home to try this which doesn't support sub select.
> >
> > My query so far is
> >
> > SELECT DEPTNO , count(*) AS TOTAL FROM EMP GROUP BY DEPTNO ORDER BY
 TOTAL
> > DESC LIMIT 1
> >
> > This however only gives me the DEPTNO only of the department which has
> > highest employee. How do I go about to select DEPTNAME now. Also the
 LIMIT
> > 1 construct is probably not supported by oracle.
> >
> > If anyone can formulate the query in oracle. I can test it at my work.
> >
> > pratik
Received on Mon May 27 2002 - 04:52:57 CEST