Fazer consulta para agrupar dados por mes
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!
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
Curtidas 0
Respostas
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
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