Sort by month without repeating the year

Question

I would like to add the total sales in the month, but without repeating the year.

select EXTRACT(year FROM v.dataVenda),
case when EXTRACT(MONTH FROM v.dataVenda) =1 then sum(v.valorvenda) else 0 end as Jan,
case when EXTRACT(MONTH FROM v.dataVenda)= 2 then sum(v.valorvenda) else 0 end as Fev,
case when EXTRACT(MONTH FROM v.dataVenda)= 3 then sum(v.valorvenda) else 0 end as Mar,
case when EXTRACT(MONTH FROM v.dataVenda)= 4 then sum(v.valorvenda) else 0 end as Abr,
case when EXTRACT(MONTH FROM v.dataVenda)= 5 then sum(v.valorvenda) else 0 end as Mai,
case when EXTRACT(MONTH FROM v.dataVenda)= 6 then sum(v.valorvenda) else 0 end as Jun,
case when EXTRACT(MONTH FROM v.dataVenda)= 7 then sum(v.valorvenda) else 0 end as Jul,
case when EXTRACT(MONTH FROM v.dataVenda)= 8 then sum(v.valorvenda) else 0 end as Ago,
case when EXTRACT(MONTH FROM v.dataVenda)= 9 then sum(v.valorvenda) else 0 end as Set,
case when EXTRACT(MONTH FROM v.dataVenda)= 10 then sum(v.valorvenda) else 0 end as Out,
case when EXTRACT(MONTH FROM v.dataVenda)= 11 then sum(v.valorvenda) else 0 end as Nov,
case when EXTRACT(MONTH FROM v.dataVenda)= 12 then sum(v.valorvenda) else 0 end as Dez

from venda v

group by EXTRACT(year FROM v.dataVenda), EXTRACT(MONTH FROM v.dataVenda)

Exit:

ano   jan feb mar abr mai jun jul ago set out nov dez

2017; 0;  0; 0;  0;  0;  10;  0; 0;   0;  0;  0;  0                                    
2018; 0;  0; 0;  0;  0;  0;   0; 224; 0;  0;  0;  0

2018; 0;  0; 0;  0;  0;  0;   0; 0;   4;  0;  0;  0

2018; 0;  0; 0;  0;  0;  8;   0; 0;   10; 0;  0;  0

I would like the date to be the year and the total of each month:

ano jan feb mar abr mai jun jul ago set out nov dez                                            

2017; 0; 0 ;0; 0; 0; 10; 0 ;0 ;0; 0; 0; 0   
2018; 0; 0; 0; 0; 0 ;8 ;0; 224; 14; 0; 0; 0

Answer 1

What happens is that new lines will be created for each term in the GROUP BY which was added, therefore with the EXTRACT MONTH who is in your query, one SET results will be created for each month that is found in the results. Then you should take that term out of there. When you do this the bank will ask for the grouping again because its SUM is being done in the wrong place. You should sum up outside the CASE. The result will be similar to the following:

SELECT EXTRACT(YEAR FROM v.dataVenda),
       SUM(CASE WHEN EXTRACT(MONTH FROM v.dataVenda) = 1 then v.valorvenda else 0 end) as Jan,
       -- ... outros meses
       SUM(CASE WHEN EXTRACT(MONTH FROM v.dataVenda) = 12 then v.valorvenda else 0 end) as Dez,
  FROM venda v
 GROUP BY EXTRACT(YEAR FROM v.dataVenda)