SQL Query to Make Month Wise Report

A month-wise report in SQL displays data grouped by month. It helps count the number of employees who joined in each month, making it easier to understand monthly joining records.

• Shows the number of employees hired each month.
• Makes it easy to compare monthly joining records.

First, we will create an employees table with the columns employee_name, phone_number, joining_date and designation and insert sample employee records to create a month-wise report.

Example 1 : Month-Wise Report Using Month Numbers

The EXTRACT() function retrieves the month number from each joining date, while COUNT(*) counts the employees who joined in each month.

Query:

SELECT
    EXTRACT(MONTH FROM joining_date) AS month,
    COUNT(*) AS total_employees
FROM employees
GROUP BY EXTRACT(MONTH FROM joining_date)
ORDER BY month;

Output:

Example 2: Month-Wise Report Using Month Names

To display month names instead of numbers, use the TO_CHAR() function. It converts the joining date into the full month name.

Query:

SELECT
    EXTRACT(MONTH FROM joining_date) AS month_number,
    TO_CHAR(joining_date, 'Month') AS month_name,
    COUNT(*) AS total_employees
FROM employees
GROUP BY
    EXTRACT(MONTH FROM joining_date),
    TO_CHAR(joining_date, 'Month')
ORDER BY month_number;

Output: