find the employee count based on hire date In Oracle-CodePudding

I want to find out the employee count who are all joined between January month to December month for the year(2020). suppose if any employee is not joined on any particular month,the count should display as 0.

Empno Ename  Hiredate     deptno
101   ram    11-Jan-20    10
102   kumar  07-Mar-20    10
103   Raja   06-Oct-20    20
104   Sasi   16-Dec-20    20
105   manoj  19-Dec-20    10

Excepted Output

Count  Hiredate_Month
1      Jan
0      Feb
1      Mar
0      Apr
0      May
0      Jun
0      Jul
0      Aug
0      Sep
1      Oct
0      Nov
2      Dec

I tried below things.

create table emp_details(empno number,ename varchar2(22),hiredate date,deptno number);

insert into emp_details values(101,'ram','11-jan-2020',10);
insert into emp_details values(102,'kumar','07-mar-2020',10);
insert into emp_details values(103,'raja','06-oct-2020',20);
insert into emp_details values(104,'sasi','16-dec-2020',20);
insert into emp_details values(105,'manoj','19-dec-2020',10);

select count(*),to_char(hiredate,'mon') from emp_details group by
to_char(hiredate,'mon')

Above query is not displaying count 0 for the months whose employee is not joined.

CodePudding user response：

In Scott's sample schema, there's the EMP table:

SQL> select ename, hiredate
  2  from emp
  3  order by to_char(hiredate, 'mm');

ENAME      HIREDATE
---------- --------
ADAMS      12.01.83   --> Adams and Miller
MILLER     23.01.82   --> were hired in January
ALLEN      20.02.81
WARD       22.02.81
                      --> nobody was hired in March
JONES      02.04.81   --> Jones was hired in April
BLAKE      01.05.81
CLARK      09.06.81
TURNER     08.09.81
MARTIN     28.09.81
KING       17.11.81
SCOTT      09.12.82
SMITH      17.12.80
JAMES      03.12.81
FORD       03.12.81

14 rows selected.

In order to get result you want, you need a calendar - separate table which contains all months in a year because table with employees doesn't contain all months (see above - nobody was hired in e.g. March).

Then you'd outer join that calendar with the original table, count number of employees and - that's it:

SQL> with calendar as
  2    (select lpad(level, 2, '0') mon
  3     from dual
  4     connect by level <= 12
  5    )
  6  select to_char(to_date(c.mon, 'mm'), 'Mon', 'nls_date_language = english') hiredate_month,
  7    count(e.empno) cnt
  8  from calendar c left join emp e on to_char(e.hiredate, 'mm') = c.mon
  9  group by c.mon
 10  order by c.mon;

HIREDATE_MON        CNT
------------ ----------
Jan                   2
Feb                   2
Mar                   0
Apr                   1
May                   1
Jun                   1
Jul                   0
Aug                   0
Sep                   2
Oct                   0
Nov                   1
Dec                   4

12 rows selected.

SQL>