SQL Query to fetch number of employees joined over a calender year, broken down per month.

Question

I'm trying to find the number of employees joined over a calender year, broken down on a monthly basis. So if 15 employees had joined in January, 30 in February and so on, the output I'd like would be

Month |  Employees
------|-----------
Jan   |      15
Feb   |      30

I've come up with a query to fetch it for a particular month

SELECT * FROM (
SELECT COUNT(EMP_NO), EMP_JN_DT
  FROM EMP_REG WHERE 
 EMP_JN_DT between '01-NOV-09'  AND '30-NOV-09'
GROUP BY EMP_JN_DT )
ORDER BY 2

How do I extend this for the full calender year?

Answer 1

SELECT   Trunc(EMP_JN_DT,'MM') Emp_Jn_Mth,
         Count(*)
FROM     EMP_REG
WHERE    EMP_JN_DT between date '2009-01-01' AND date '2009-12-31'
GROUP BY Trunc(EMP_JN_DT,'MM')
ORDER BY 1;

If you do not have anyone join in a particular month then you'd get no row returned. To over come this you'd have to outerjoin the above to a list of months in the required year.

Answer 2

http://www.techonthenet.com/oracle/functions/extract.php

There is a function that returns month. What you need to do is just put it in group by

Answer 3

SELECT to_date(EMP_JN_DT,'MON') "Month", EMP_NO "Employees"
FROM EMP_REG
WHERE EMP_JN_DT between date '2009-01-01' AND date '2009-12-31'
GROUP by "Month"
ORDER BY 1;