Mysql Question posted in
The official MySQL documentation can be found here.

MySQL fetch employee count in the last available month in each year

I have a table with column year, month, employee_count. I want to fetch employee_count from the latest month available in each year

From this:

year month employee_count
2010 1 230
2013 6 1205
2022 1 200
2023 1 20
2013 5 123
2010 11 33
2023 10 45

I need this:

year month employee_count
2010 11 33
2013 6 1205
2022 1 200
2023 10 45
Tags:

2

Answers


  1. 0

    Based on your description of the issue, and your desired output:

    • With a subquery (latest_month_per_year) retrieve the latest month (MAX(month)) for each year by grouping the data by year.
    • And then the main query joins the original table with the subquery on year and month, so that it fetches the row with the latest month for each year.
    SELECT t.year, t.month, t.employee_count
FROM employee_table t
JOIN (
    SELECT year, MAX(month) AS latest_month
    FROM employee_table
    GROUP BY year
) AS latest_month_per_year
ON t.year = latest_month_per_year.year AND t.month = latest_month_per_year.latest_month;

    As an outcome this will give you the latest employee_count per each year.

    Login or Signup to reply.
  2. 0

    I tried the below query with RIGHT JOIN which seems to work:

    SELECT B.year, A.month, A.employee_count
FROM 
  EMPLOYEE A 
    RIGHT JOIN
  (SELECT MAX(month) as max_month, year
   FROM 
     EMPLOYEE 
   GROUP BY year) B
ON (A.month = B.max_month AND A.year = B.year);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search