sql – Sort by month without repeating the year

Question:

I would like to add up the total sales for 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 output to be the year and the total for 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:

What happens is that new lines will be created for each term in the GROUP BY that was added, so with the EXTRACT MONTH that is in your query , a SET of results will be created for each month that is found in the results. So you should get that term out of there. When you do this, the bank will ask for the grouping again because your SUM is being done in the wrong place. You must sum 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)
Scroll to Top
AllEscort