Fazer consulta para agrupar dados por mes

SQL Server

13/09/2009

Data------------------------Vendas
2009-05-01 00:00:00--------200
2009-05-26 00:00:00--------133
2009-06-05 00:00:00--------25
2009-01-30 00:00:00--------13

Preciso trazer os dados da seguinte maneira
mes 01 = 13
mes 02 = 0
mes 03 = 0
mes 04 = 0
mes 05 = 333
mes 06 = 0
mes 07 = 0
mes 08 = 0
mes 09 = 0
mes 10 = 0
mes 11 = 0
mes 12 = 0

SELECT Data,
Vendas
From Pedidos
Group by month(Data)

[size=18:23a42b1338]Eu preciso que mesmo que não possua registros para os meses 02, 03, etc, na consulta me traga como ZERO. Seria para montar um gráfico, por isso a necessidade da consulta ordenada mês a mês[/size:23a42b1338]

OBRIGADO!


Ale100001

Ale100001

Curtidas 0

Respostas

Renato Tavares

Renato Tavares

13/09/2009

Segue: 8)


drop table nova;

create temp table nova (data date, valor decimal(10,2));

insert into nova values (´01/05/2009´,200.00);
insert into nova values (´26/05/2009´,133.00);
insert into nova values (´05/06/2009´,25.00);
insert into nova values (´30/01/2009´,13.00);

select *from nova;

select month(data) meses,
sum(case when month(data) = 1 then valor
when month(data) = 2 then valor
when month(data) = 3 then valor
when month(data) = 4 then valor
when month(data) = 5 then valor
when month(data) = 6 then valor
when month(data) = 7 then valor
when month(data) = 8 then valor
when month(data) = 9 then valor
when month(data) = 10 then valor
when month(data) = 11 then valor
when month(data) = 12 then valor end )as valor
from nova
group by 1

union all

select distinct 2 as meses,0.00
from nova

union all

select distinct 3 as meses,0.00
from nova

union all

select distinct 4 as meses,0.00
from nova

union all

select distinct 7 as meses,0.00
from nova

union all

select distinct 8 as meses,0.00
from nova

union all

select distinct 9 as meses,0.00
from nova

union all

select distinct 10 as meses,0.00
from nova

union all

select distinct 11 as meses,0.00
from nova

union all

select distinct 12 as meses,0.00
from nova

order by 1


GOSTEI 0
POSTAR