duvida em select

Firebird

30/06/2010

Boa tarde,   tenho o seguinte problema:   uma tabela com os campos:   valor decimal(10,2)   data date   gostaria de fazer um totalizador por meses destes valores:   select SUM(t.valor), extract(month from data) as mes from tabela t where extract(year from data) = 2010 group by extract(month from data)   até ai tudo bem, este select vai me retornar a soma por mes do ano de 2010   agora o problema:   os meses que não possuem nenhum registro nao aparecem gostaria que aparecessem mesmo que com o valor zerado   ------------------------------------------ oq me retorna:   mes  valor 1       20,00 2       30,00 3       40,00 4       50,00   --------------------------------------- oq eu gostaria de receber   mes   valor 1        20,00 2        30,00 3        40,00 4        50,00 5         0,00 6         0,00 7         0,00        8         0,00 9         0,00 10       0,00 11       0,00 12       0,00     obrigado, paulo
Paulo Candido

Paulo Candido

Curtidas 0

Respostas

Wilson Junior

Wilson Junior

30/06/2010

Identico ao https://www.devmedia.com.br/forum/viewtopic.asp?id=380354
GOSTEI 0
Tiago Melo

Tiago Melo

30/06/2010

use o coalesce no seu SUM....tipo select coalesce(sum(Campo_Somado),0) from Tabela
GOSTEI 0
Tiago Melo

Tiago Melo

30/06/2010

só pra esclarecer derrepente vc não venha a saber o porque do coalesce...essa função retorna o primeiro nao nulo, ou seja se o valor da sua soma retornar nulo, ele retorna o segundo argumento, que nesse exemplo que te enviei é zero, mas poderia ser qualquer outro valor...
GOSTEI 0
Paulo Candido

Paulo Candido

30/06/2010

use o coalesce no seu SUM....tipo select coalesce(sum(Campo_Somado),0) from Tabela
Infelismente, já tentei e não adiantou.   pensei em criar uma tabela auxiliar com numeros de 1 a 12 e fazer uma ligação, do forma que o mes se relacione a cada valor desta tabela auxiliar, forcando o resultado a ter 12 tuplas, porem nao tenho essa tabela a disposicao no meu banco, será que não existe essa tabela nativa no firebird ou algo parecido??     p.s.  desculpe pelo post anterior, usei o tab para criar tabulação e ele mudou o foco para o botao de gravar sem eu ver...     obrigado
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

30/06/2010

você pode, ao invés de linhas, usar colunas para exibir o resultado

select
  sum(case when extract(month from DATA)=1 then VALOR else 0 end) mes_01,
  sum(case when extract(month from DATA)=2 then VALOR else 0 end) mes_02,
  sum(case when extract(month from DATA)=3 then VALOR else 0 end) mes_03,
  sum(case when extract(month from DATA)=4 then VALOR else 0 end) mes_04,
  sum(case when extract(month from DATA)=5 then VALOR else 0 end) mes_05,
  sum(case when extract(month from DATA)=6 then VALOR else 0 end) mes_06,
  sum(case when extract(month from DATA)=7 then VALOR else 0 end) mes_07,
  sum(case when extract(month from DATA)=8 then VALOR else 0 end) mes_08,
  sum(case when extract(month from DATA)=9 then VALOR else 0 end) mes_09,
  sum(case when extract(month from DATA)=10 then VALOR else 0 end) mes_10,
  sum(case when extract(month from DATA)=11 then VALOR else 0 end) mes_11,
  sum(case when extract(month from DATA)=12 then VALOR else 0 end) mes_12
from
  FINANCEIRO
where
  extract(year from DATA) = 2010


ou, para exibir os resultados de todos os anos em que haja algum registro:

select
  extract(year from DATA) ano,
  sum(case when extract(month from DATA)=1 then VALOR else 0 end) mes_01,
  sum(case when extract(month from DATA)=2 then VALOR else 0 end) mes_02,
  sum(case when extract(month from DATA)=3 then VALOR else 0 end) mes_03,
  sum(case when extract(month from DATA)=4 then VALOR else 0 end) mes_04,
  sum(case when extract(month from DATA)=5 then VALOR else 0 end) mes_05,
  sum(case when extract(month from DATA)=6 then VALOR else 0 end) mes_06,
  sum(case when extract(month from DATA)=7 then VALOR else 0 end) mes_07,
  sum(case when extract(month from DATA)=8 then VALOR else 0 end) mes_08,
  sum(case when extract(month from DATA)=9 then VALOR else 0 end) mes_09,
  sum(case when extract(month from DATA)=10 then VALOR else 0 end) mes_10,
  sum(case when extract(month from DATA)=11 then VALOR else 0 end) mes_11,
  sum(case when extract(month from DATA)=12 then VALOR else 0 end) mes_12
from
  FINANCEIRO
group by
  extract(year from DATA)


no lugar de mes_xx você pode ainda colocar o nome do mês - completo ou abreviado - para que o resultado se mostre ainda mais claro.
GOSTEI 0
Paulo Candido

Paulo Candido

30/06/2010

você pode, ao invés de linhas, usar colunas para exibir o resultado

select
  sum(case when extract(month from DATA)=1 then VALOR else 0 end) mes_01,
  sum(case when extract(month from DATA)=2 then VALOR else 0 end) mes_02,
  sum(case when extract(month from DATA)=3 then VALOR else 0 end) mes_03,
  sum(case when extract(month from DATA)=4 then VALOR else 0 end) mes_04,
  sum(case when extract(month from DATA)=5 then VALOR else 0 end) mes_05,
  sum(case when extract(month from DATA)=6 then VALOR else 0 end) mes_06,
  sum(case when extract(month from DATA)=7 then VALOR else 0 end) mes_07,
  sum(case when extract(month from DATA)=8 then VALOR else 0 end) mes_08,
  sum(case when extract(month from DATA)=9 then VALOR else 0 end) mes_09,
  sum(case when extract(month from DATA)=10 then VALOR else 0 end) mes_10,
  sum(case when extract(month from DATA)=11 then VALOR else 0 end) mes_11,
  sum(case when extract(month from DATA)=12 then VALOR else 0 end) mes_12
from
  FINANCEIRO
where
  extract(year from DATA) = 2010


ou, para exibir os resultados de todos os anos em que haja algum registro:

select
  extract(year from DATA) ano,
  sum(case when extract(month from DATA)=1 then VALOR else 0 end) mes_01,
  sum(case when extract(month from DATA)=2 then VALOR else 0 end) mes_02,
  sum(case when extract(month from DATA)=3 then VALOR else 0 end) mes_03,
  sum(case when extract(month from DATA)=4 then VALOR else 0 end) mes_04,
  sum(case when extract(month from DATA)=5 then VALOR else 0 end) mes_05,
  sum(case when extract(month from DATA)=6 then VALOR else 0 end) mes_06,
  sum(case when extract(month from DATA)=7 then VALOR else 0 end) mes_07,
  sum(case when extract(month from DATA)=8 then VALOR else 0 end) mes_08,
  sum(case when extract(month from DATA)=9 then VALOR else 0 end) mes_09,
  sum(case when extract(month from DATA)=10 then VALOR else 0 end) mes_10,
  sum(case when extract(month from DATA)=11 then VALOR else 0 end) mes_11,
  sum(case when extract(month from DATA)=12 then VALOR else 0 end) mes_12
from
  FINANCEIRO
group by
  extract(year from DATA)


no lugar de mes_xx você pode ainda colocar o nome do mês - completo ou abreviado - para que o resultado se mostre ainda mais claro.
    cara, muito boa a idéia, porém ainda num é o que eu tava precisando, precisava que esses valores se propagassem em linhas, sendo as coluas apenas {mes valor}. mas em cima da sua ideia, eu pensei em uma outra solução. funcionou como eu queria, so vou deixar o post aberto para ver se alguem consegue otmizar essa consulta. mas quanto a funcionalidade, é exatamente o que preciso     select coalesce(sum(valor),0), 'JAN' from tabela where extract(year from data) = 2010 and extract(month from data) = 1   union all   select coalesce(sum(valor),0), 'FEV' from tabela where extract(year from data) = 2010 and extract(month from data) = 2   union all   ...   union all   select coalesce(sum(valor),0), 'DEZ' from tabela where extract(year from data) = 2010 and extract(month from data) = 12       Muito obrigado pelo post
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

30/06/2010

não sei se é interessante pra você, mas outra opção seria criar uma stored procedure:

CREATE PROCEDURE TOTAL_MENSAL
returns (
    ano smallint,
    mes smallint,
    valor numeric(15,2))
as
declare variable mes_01 numeric(15,2);
declare variable mes_02 numeric(15,2);
declare variable mes_03 numeric(15,2);
declare variable mes_04 numeric(15,2);
declare variable mes_05 numeric(15,2);
declare variable mes_06 numeric(15,2);
declare variable mes_07 numeric(15,2);
declare variable mes_08 numeric(15,2);
declare variable mes_09 numeric(15,2);
declare variable mes_10 numeric(15,2);
declare variable mes_11 numeric(15,2);
declare variable mes_12 numeric(15,2);
declare variable i smallint;
begin
  for
    select
      extract(year from DATA) ano,
      sum(case when extract(month from DATA)=1 then VALOR else 0 end) mes_01,
      sum(case when extract(month from DATA)=2 then VALOR else 0 end) mes_02,
      sum(case when extract(month from DATA)=3 then VALOR else 0 end) mes_03,
      sum(case when extract(month from DATA)=4 then VALOR else 0 end) mes_04,
      sum(case when extract(month from DATA)=5 then VALOR else 0 end) mes_05,
      sum(case when extract(month from DATA)=6 then VALOR else 0 end) mes_06,
      sum(case when extract(month from DATA)=7 then VALOR else 0 end) mes_07,
      sum(case when extract(month from DATA)=8 then VALOR else 0 end) mes_08,
      sum(case when extract(month from DATA)=9 then VALOR else 0 end) mes_09,
      sum(case when extract(month from DATA)=10 then VALOR else 0 end) mes_10,
      sum(case when extract(month from DATA)=11 then VALOR else 0 end) mes_11,
      sum(case when extract(month from DATA)=12 then VALOR else 0 end) mes_12
    from
      TABELA
    group by
      extract(year from DATA)
    into :ano, :mes_01, :mes_02, :mes_03, :mes_04, :mes_05, :mes_06,
         :mes_07, :mes_08, :mes_09, :mes_10, :mes_11, :mes_12 do
  begin
    i = 1;
    while (i <= 12) do
    begin
      mes = i;
      valor = (case i when 1 then mes_01
                      when 2 then mes_02
                      when 3 then mes_03
                      when 4 then mes_04
                      when 5 then mes_05
                      when 6 then mes_06
                      when 7 then mes_07
                      when 8 then mes_08
                      when 9 then mes_09
                      when 10 then mes_10
                      when 11 then mes_11
                      when 12 then mes_12
               end);
      suspend;
      i = i+1;
    end
  end
end


e você pode usá-la como uma tabela:

select * from total_mensal

select * from total_mensal where ano = 2010

select mes, valor from total_mensal where ano = 2009

select valor, mes from total_mensal where ano = 2010 order by mes desc




GOSTEI 0
POSTAR