Sort by month without repeating the year

Question

Sort by month without repeating the year

Navigation

#1 by (1 votes)

3

I would like to add up the total sales in the month, but not repeat 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)

Output:

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

sql postgresql

asked by anonymous 22.08.2018 / 15:02

1 answer

Error return method "public async Taskstring" Why the "quotation marks" in the INT data?

score 1 · Accepted 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 take that term from there. When you do this the bank will ask for the grouping again because its SUM is being done in the wrong place. You must add out CASE . The result will look 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)