How to unify the results of Count POSTGRES?

Question

Good morning, I need this query to return the unified result, different from what it returns to me, does anyone have any idea how to do this in Postgres?? went until May(May) but continues until the end of the months, my parameter $1 is YEAR .

RETURN QUERY SELECT DC.cod_descricao_status, DC.nome,
 
        (CASE WHEN TEMP.ano IS NULL THEN $1 ELSE TEMP.ano END) AS "ano",
        (CASE WHEN TEMP.jan IS NULL THEN 0 ELSE TEMP.jan END) AS "jan",
        (CASE WHEN TEMP.fev IS NULL THEN 0 ELSE TEMP.fev END) AS "fev",
        (CASE WHEN TEMP.mar IS NULL THEN 0 ELSE TEMP.mar END) AS "mar",
        (CASE WHEN TEMP.abr IS NULL THEN 0 ELSE TEMP.abr END) AS "abr",
        (CASE WHEN TEMP.mai IS NULL THEN 0 ELSE TEMP.mai END) AS "mai"
FROM public.pla_descricao_status_tarefa AS DC  


        
        INNER JOIN
        
    (SELECT S.cod_descricao_status, D.nome,             EXTRACT(YEAR FROM S.data_alteracao) AS "ano",
    COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 1) THEN S.data_alteracao END) AS "jan",
    COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 2) THEN S.data_alteracao END) AS "fev",
    COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 3) THEN S.data_alteracao END) AS "mar",
    COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 4) THEN S.data_alteracao END) AS "abr",
    COUNT(CASE WHEN(EXTRACT(MONTH FROM (S.data_alteracao)) = 5) THEN S.data_alteracao END) AS "mai",

    COUNT(S.data_alteracao) AS TOTAL
    FROM (SELECT DISTINCT ON (cod_descricao_status, cod_tarefa)cod_descricao_status, cod_tarefa, data_alteracao FROM public.pla_status_tarefa) AS S 
    INNER JOIN public.pla_tarefa AS T ON T.cod_tarefa = S.cod_tarefa
    INNER JOIN public.pla_descricao_status_tarefa AS D ON D.cod_descricao_status = S.cod_descricao_status
    WHERE (S.cod_descricao_status = 1 OR S.cod_descricao_status = 7 OR S.cod_descricao_status = 4) AND T.custo_executante LIKE CONCAT($2,'%') AND T.custo_executante NOT LIKE '101'
    AND T.flg_excluido = '0'
    AND EXTRACT(YEAR FROM S.data_alteracao) = $1
    AND T.cod_tarefa IN (SELECT S1.cod_tarefa FROM public.pla_status_tarefa AS S1 WHERE S1.cod_descricao_status = 1)
    GROUP BY S.cod_descricao_status, D.nome, S.data_alteracao)
    AS TEMP ON DC.cod_descricao_status = TEMP.cod_descricao_status
    WHERE DC.cod_descricao_status = 1 OR DC.cod_descricao_status = 7 OR DC.cod_descricao_status = 4;
    END;

he returns to me like this :

(1,"ENV - Enviado",2020,0,0,0,0,0,0,0,0,0,0,1,0)

(1,"ENV - Enviado",2020,0,0,0,0,0,0,0,0,0,0,1,0)

(1,"ENV - Enviado",2020,0,0,0,0,0,0,0,0,0,0,1,0)

(4,"CAN - Cancelado",2020,0,0,0,0,0,0,0,1,0,0,0,0)

(4,"CAN - Cancelado",2020,0,0,0,0,0,0,0,1,0,0,0,0)

(4,"CAN - Cancelado",2020,0,0,0,0,0,0,0,1,0,0,0,0)

(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,1,0,0,0)

(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,1,0,0,0)

(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,1,0,0,0)

(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,1,0,0,0)

I want to unify them:

(1,"ENV - Enviado",2020,0,0,0,0,0,0,0,0,0,0,3,0)

(4,"CAN - Cancelado",2020,0,0,0,0,0,0,0,3,0,0,0,0)

(7,"FIN - Finalizado",2020,0,0,0,0,0,0,0,0,4,0,0,0)

Thanks in advance!

Answer 1

Since the goal is:

Unifying the results of Count

Since your query is already resulting in the partial information you need, just perform the grouping of this partial result.

One possibility is to consider the result of your existing query as if it were a table, which I will call r remembering the word result:

select cod_descricao_status, nome, ano, sum(jan) as janeiro, sum(fev) as fevereiro, sum(mar) as marco, sum(abr) as abril
  from (
    //Sua consulta original entra aqui
  ) as r
  group by r.ano, r.cod_descricao_status, r.nome
  order by r.ano, r.cod_descricao_status, r.nome

I did not analyze your query to see if there are improvements to be made. I have observed only what can be done to obtain the expected result.