## 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

```

Method/Workaround #1: Recursive WITH clause

```WITH x(mm, mon, hires) AS (
SELECT 1 as mm, 'JAN' AS mon, COUNT(*) hires
FROM emp
WHERE EXTRACT(MONTH FROM hiredate)=1
UNION ALL
(SELECT COUNT(*)
FROM emp
WHERE EXTRACT(MONTH FROM hiredate)=x.mm+1)
FROM x
WHERE x.mm+1<=12
)
SELECT mon, hires
FROM x
/
MON               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
```

Method/Workaround #2: LEFT JOIN

```WITH x AS (
SELECT LEVEL mm, TO_CHAR(ADD_MONTHS(DATE'2000-01-01', LEVEL-1), 'MON') mon
FROM dual
CONNECT BY LEVEL<=12
)
SELECT x.mon, COUNT(e.empno) hires
FROM x LEFT JOIN emp e ON x.mm=EXTRACT(MONTH FROM e.hiredate)
GROUP BY x.mon, x.mm
ORDER BY x.mm
/
MON               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
```

Method/Workaround #3: UNION ALL

```WITH x AS (
SELECT LEVEL mm, TO_CHAR(ADD_MONTHS(DATE'2000-01-01', LEVEL-1), 'MON') mon, 0 as hires
FROM dual
CONNECT BY LEVEL<=12
UNION ALL
SELECT EXTRACT(MONTH FROM hiredate), TO_CHAR(hiredate, 'MON'), COUNT(*)
FROM emp
GROUP BY EXTRACT(MONTH FROM hiredate), TO_CHAR(hiredate, 'MON')
)
SELECT mon, MAX(hires) AS hires
FROM x
GROUP BY mm, mon
ORDER BY mm
/
MON               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

```

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.