BEGIN;
CREATE TABLE task (
id int,
"type" text,
building text,
);
CREATE TABLE task_history (
task_id int,
year int,
month int,
day int,
status text
);
SELECT
task_history.year -- ou EXTRACT(year from ...) si il y a juste une colonne date
, task_history.month -- same
, task."type"
, task.building
, COUNT(CASE task_history.status WHEN 'closed' THEN 1 END) AS nb_closed
, COUNT(CASE task_history.status WHEN 'reopened' THEN 1 END) AS nb_reopened
FROM task
JOIN task_history
ON task.id = task_history.task_id
GROUP BY
task_history.year -- ou EXTRACT(year from ...) si il y a juste une colonne date
, task_history.month -- same
, task."type"
, task.building
ORDER BY
task_history.year -- ou EXTRACT(year from ...) si il y a juste une colonne date
, task_history.month -- same
, task."type"
, task.building