Home » SQL & PL/SQL » SQL & PL/SQL » unable to get highest no. of emp
unable to get highest no. of emp [message #204069] Fri, 17 November 2006 11:10 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello team

i have tried but unable to get highest no of emp of among the dept of emp table.
can any body help me where i had done mistake??


SQL>  SELECT * FROM EMP1;

CLIENT NAME                    SAL    DEPT_NO        EMP JOB
------ ---------------- ---------- ---------- ---------- --------
C00001 Ivan Bayross         150000         10       2000 clerk
C00002 Vandana Saitwal                     30        200 manager
C00003 Paramada Jaguste     149000                  3500 clerk
C00004 Basu Navindgi        400000         10        500 manager
C00005 Ravi Sreedharan        4000         20       5000 analyst
C00006 Rukmini              200000         40        500 manager
C00007 Sumant                14000         20        700 analyst
C00008 Sushil                18000         40        200 manager
C00009 Rupak                300000         20        200 clerk
C00010 Rupali                90000         40        600 analyst
C00011 Ramesh               190000         10       3980 manager
C00012 Rupesh                80000         30        580 clerk
C00013 Ritika               376532         20        390  supreme
C00014 Shiva                600000         30        300 manager

14 rows selected.

SQL> select max(count(emp)) from emp1 group by dept_no;

MAX(COUNT(EMP))
---------------
              4

SQL> select dept_no from emp1 
  2  group by dept_no 
  3  having count(emp)=(select max(count(emp)) from emp1 group by dept_no);

   DEPT_NO
----------
        20
        30



with regards
ashish

[Updated on: Fri, 17 November 2006 11:11]

Report message to a moderator

Re: unable to get highest no. of emp [message #204072 is a reply to message #204069] Fri, 17 November 2006 12:00 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi ,

how are you and howz is ur preps going. You can try one of the way i am sending to you.

select deptno,count(empno) from emp
having count(empno) = (select max(count(deptno)) from emp
group by deptno)
group by deptno


Bye
Ashu
Re: unable to get highest no. of emp [message #204076 is a reply to message #204069] Fri, 17 November 2006 12:17 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thanxx ashu...

after a long time. hw r u ??

thanxx once again

bye
ashish
Re: unable to get highest no. of emp [message #204088 is a reply to message #204069] Fri, 17 November 2006 13:39 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello ashu

i m getting the same result as what i had done.
but i want no of emp among the dept which is highest .
so if possible suggest me some other hints .

thanx for ur possitive response..


with regards
ashish

[Updated on: Fri, 17 November 2006 13:39]

Report message to a moderator

Re: unable to get highest no. of emp [message #204096 is a reply to message #204069] Fri, 17 November 2006 15:37 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select cnt
from
(select count(*) cnt,deptno
from emp1
group by deptno
order by cnt desc)
where rownum = 1;
Re: unable to get highest no. of emp [message #204111 is a reply to message #204069] Fri, 17 November 2006 21:03 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello

i m giving u few more example about my question so that u clear about it..

SQL>ed
Wrote file afiedt.buf

  1  select dept_no, sum(emp) from emp1
  2* group by rollup (dept_no, emp)
SQL>/

   DEPT_NO   SUM(EMP)
---------- ----------
        10        500
                 2000
                 3980
                 6480
        20        200
                  390
                  700
                 5000
                 6290
        30        200
                  300
                  580
                 3500
                 4580
        40        200
                  500
                  600
                 1300
                18650

19 rows selected


here dept_no 40 having 18650 no of emp. which highest among the dept. so suggest me how could i excute it by sql query not to get the whole dept.

this makes u clear abt my qestion..

with regards
ashish
Re: unable to get highest no. of emp [message #204118 is a reply to message #204069] Fri, 17 November 2006 23:32 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

DEPT_NO SUM(EMP)
---------- ----------
10 500
2000
3980
6480 -- total of dept 10
20 200
390
700
5000
6290 -- total of dept 20
30 200
300
580
3500
4580 -- total of dept 30
40 200
500
600
1300 -- total of dept 40
18650 -- total of dept 10,20,30,40

19 rows selected

here dept_no 40 having 18650 no of emp. which highest among the dept.


18650 is the total number of employees of all the 4 departments. It is not the total of dept 40.
Re: unable to get highest no. of emp [message #204142 is a reply to message #204069] Sat, 18 November 2006 03:17 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
oh oh .sorry

u r right rameshuddaraju

suppose dept 10 having highest no of emp i.e 6480.

i want to get the details of dept no.
so how could i get

please reply me back

with regards
ashish
Re: unable to get highest no. of emp [message #204146 is a reply to message #204142] Sat, 18 November 2006 03:35 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi ashish,

according toy you want the details of the department who has maximum number of employees. You find out the deptno qualify your output and send that output as input to Department table.

I am giving an example try this.

select * from dept where deptno in
(select deptno from emp
having count(empno) in
(select max(count(deptno)) from emp
group by deptno)
group by deptno)
/


Bye,
Ashu
Re: unable to get highest no. of emp [message #204162 is a reply to message #204069] Sat, 18 November 2006 05:55 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member


Hi,
Try this


SQL > select dept_no, sum(emp) from emp1
group by dept_no having sum(emp) = (select max(sum(emp)) from emp
group by dept_no)



Re: unable to get highest no. of emp [message #204166 is a reply to message #204069] Sat, 18 November 2006 08:52 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
thanxx ashu & rameshuddaraju

i will give y my feedback after solving this query..

i m very happy by ur immmediate response.

with regards
ashish
Re: unable to get highest no. of emp [message #204178 is a reply to message #204069] Sat, 18 November 2006 10:52 Go to previous message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
hello ashu & rameshuddaraju

i got my answer by ur help but little modification i have done.
check it.

SQL>select * from emp1 where emp in(select max(emp) from emp1 where dept_no in(select dept_no from emp1
group by dept_no having count(emp) in (
select max(count(emp)) from emp1
group by dept_no)))
/

thanxx

with regards
ashish
Previous Topic: PL/SQL problem
Next Topic: error message
Goto Forum:
  


Current Time: Tue Dec 06 04:37:50 CST 2016

Total time taken to generate the page: 0.10641 seconds