postgresql count number of employees per year-CodePudding

Columns: id, first_name, last_name, start_date, end_date NULL allowed only in end-date.

This works for showing how many people started in a particular year:

SELECT
  to_char(date_trunc('year', start_date)::date, 'YYYY') AS "Year",
  COUNT(*) AS "New Employees"
FROM employees 
GROUP BY date_trunc('year', start_date)
ORDER BY date_trunc('year', start_date) ASC

I cannot find a solution to display a list of years with the total number of employees in a particular year. The assumption is that e.g. in 2022 I would count those with end_date = null AND end_date = 2022.

CodePudding user response：

You need a list of all relevant years, that is to say, all years from the earliest starting date until the current year. Then count the number of active employees for each year.

PostgreSQL provides the useful generate_series function for creating the range of years. You can use coalesce to replace an empty end_date with the current date, available from current_date. The resulting query is

with years(year) as (
  select generate_series(
    date_trunc('year', (select min(start_date) from employees)),
    date_trunc('year', current_date),
    interval '1 year'
  )
)
select to_char(year, 'yyyy') as year, count(*)
from years
join employees on start_date < year   '1 year'
  and coalesce(end_date, current_date) >= year
group by 1
order by 1;