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)