Solutions to Puzzle of the Week #5

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 mm+1, TO_CHAR(ADD_MONTHS(DATE'2000-01-01', mm), 'MON'), 
      (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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s