Puzzle of the Week #5:

Write a single SELECT statement that would list all 12 months and number of employees hired in each month. Year part should be ignored. Use emp table (in scott schema). Find as many solutions as possible.

Expected Result:

```Month  Number of hires
------ ---------------
JAN                  1
FEB                  2
MAR                  0
APR                  2
MAY                  2
JUN                  1
JUL                  0
AUG                  0
SEP                  2
OCT                  0
NOV                  1
DEC                  3
```

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

5 thoughts on “Puzzle of the Week #5 – Calendar Hiring Report”

1. Muhammad Farooq March 26, 2016 / 4:52 am

SELECT HIRE_MONTH,EMPCOUNT FROM(
selecT
TO_NUMBER(to_char(e.HIREDATE,’MM’)) MONSORT,
to_char(e.HIREDATE,’MON’) HIRE_MONTH,
COUNT(EMPNO) EMPCOUNT from emp e
GROUP BY to_char(e.HIREDATE,’MON’),
TO_NUMBER(to_char(e.HIREDATE,’MM’))
ORDER BY TO_NUMBER(to_char(e.HIREDATE,’MM’))
)

• Zahar Hilkevich March 27, 2016 / 6:17 pm

Your query only outputs 8 months out of 12

2. Ashwini Kumar March 27, 2016 / 10:35 pm

select nvl(emp.Months,mn.Months),nvl(CNT,0) from
(select extract(Month from hiredate) id,to_char(hiredate,’MON’) Months,count(*) cnt from emp
group by extract(Month from hiredate),to_char(hiredate,’MON’) ) emp,
(SELECT to_char(to_date( level,’mm’), ‘MON’) Months,level id FROM DUAL CONNECT BY LEVEL <=12) mn
where mn.id=emp.id(+) order by mn.id;

3. Ashwini Kumar March 28, 2016 / 9:19 pm

select nvl(emp.Months,mn.Months),nvl(CNT,0) from
(select extract(Month from hiredate) id,to_char(hiredate,’MON’) Months,count(*) cnt from emp
group by extract(Month from hiredate),to_char(hiredate,’MON’) ) emp,
(SELECT to_char(to_date( level,’mm’), ‘MON’) Months,level id FROM DUAL CONNECT BY LEVEL <=12) mn
where mn.id=emp.id(+) order by mn.id;

4. sunitha March 28, 2016 / 10:05 pm

select x.mon Month,
count(hiredate) hires_per_month from
scott.emp a,
(SELECT to_char(TRUNC(SYSDATE,’y’)-1+ROWNUM,’Mon’) mon, to_char(TRUNC(SYSDATE,’y’)-1+ROWNUM,’mm’) mm
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) – TRUNC(SYSDATE,'y')
group by to_char(TRUNC(SYSDATE,'y')-1+ROWNUM,'Mon') , to_char(TRUNC(SYSDATE,'y')-1+ROWNUM,'mm')
) x
where x.mon = to_char(hiredate(+),'Mon')
group by x.mon, x.mm order by x.mm;