Query for Number of Employees

[prasathlogu]

Hi all,

Can you give me the query for
Say suppose the table "EMP" contains 2006 th year record only:

So in 2006 "On which month , the maximum number of employees have been joined in each and every department"



The output should contain the following columns:
1. Department ID
2. Month
3. emp_count .

Regards,
Prasath.

[jamesravid]

Hi try this query,

select deptno
,to_char(hiredate,'Month') month
,count(*) "NEW EMPLOYEES"
from emp
group by deptno
,to_char(hiredate,'Month')

The above query returns number of employees joined in each month for each department. You can also include year in the group by clause if you have records for more than one year.

But i could not understand,

the maximum number of employees have been joined in each and every department...

My query does not find maximum of anything...it just counts number of new hires.

[sutnarcha]

This will work

Select top 1 Month(DeleteDate) as Mnth, count(*) as Cnt from ItemsDeleted group by Month(DeleteDate) order by Count(*) DESC

[prasathlogu]

Maximum number of employees means,
say suppose in dept 10 ,
there were 5 no of emps joined in Jan,
there were 10 no of emps joined in FEb and
there were 2 no of emps joined in March.
So the O/P should get the Feb month record for dept 10, since it is having the maximum number emps count.

Hence pls give me the exact query.
Regards
Prasath

[prasathlogu]

Hi try this query,

select deptno
,to_char(hiredate,'Month') month
,count(*) "NEW EMPLOYEES"
from emp
group by deptno
,to_char(hiredate,'Month')

The above query returns number of employees joined in each month for each department. You can also include year in the group by clause if you have records for more than one year.

But i could not understand,

the maximum number of employees have been joined in each and every department...

My query does not find maximum of anything...it just counts number of new hires.

Maximum number of employees means,
say suppose in dept 10 ,
there were 5 no of emps joined in Jan,
there were 10 no of emps joined in FEb and
there were 2 no of emps joined in March.
So the O/P should get the Feb month record for dept 10, since it is having the maximum number emps count.

Hence pls give me the exact query.
Regards

[sutnarcha]

Ok got it now,

But you will have to wait until I get to work on it.