Média ponderada com SQL(não é trabalho de faculdade)

Assuntos Gerais
#1

Salve rapaziada do GUJ,
Estou com um problemao aqui no serviço,
to fazendo um relatorio IReport, que traz a quantidade de itens vendida todos os meses, porém tem mes que nao vendeu nada.
No final o chefe quer uma media ponderada onde os meses que nao tem valor nao entrariam na media.
Ou seja
janeiro : 3
fevereiro: 4
marco: 0
abril: 0
Media ponderada 3+4/2=3,5

Se tivesse 1 variavel pra ir somando 1 quando tiver registro ia ser sussa mais nao tem. Alguem tem alguma ideia?

Queryzinha:

SELECT 
	r.sigla, 
	  r.codigo_produto, 
	  r.descricao, 
	  r.complemento, 
	  r.embalagem_transporte, 
	  r.quantidade_produto, 	  
	  r.fabriNome,
	  r.cliNome,
	  r.vendNome,
	  r.ano,
	  SUM(r.soma1) as jan,
	  SUM(r.soma2) as fev,
	  SUM(r.soma3) as mar,
	  SUM(r.soma4) as abr,
	  SUM(r.soma5) as mai,
	  SUM(r.soma6) as jun,
	  SUM(r.soma7) as jul,
	  SUM(r.soma8) as ago,
	  SUM(r.soma9) as sete,
	  SUM(r.soma10) as outu,
	  SUM(r.soma11) as nov,
	  SUM(r.soma12) as dez

FROM (	  
	SELECT 
	  pcli.nome as cliNome,
          pvend.nome as vendNome,
	  est_unidade_medida.sigla, 
	  est_produto.codigo_produto, 
	  est_produto.descricao,
	  coalesce(est_produto.complemento, 'Empty') as complemento, 
	  coalesce(est_produto.embalagem_transporte, 'Empty') as embalagem_transporte,
	  fat_movimentacao_itens_saida.quantidade_produto, 	  
	  est_fabricante.nome as fabriNome,
	  est_fabricante.id_fabricante,
	  com_cliente.id_cliente,
	  est_grupo_produto.id_grupo_produto,
	  com_vendedor.id_vendedor,
	  est_subgrupo_produto.id_subgrupo_produto,
	  extract (year from fat_nota_fiscal_saida.data_movimentacao) as ano,	  
	  
	  
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 1 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma1,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 2 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma2,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 3 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma3,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 4 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma4,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 5 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma5,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 6 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma6,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 7 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma7,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 8 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma8,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 9 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma9,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 10 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma10,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 11 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma11,
	   CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 12 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma12  
	  
	FROM 
	  clarion.fat_movimentacao_itens_saida, 
	  clarion.com_pessoa as pcli, 
	  clarion.com_pessoa as pvend, 
	  clarion.com_natureza_operacao natureza, 
	  clarion.est_produto, 
	  clarion.est_fabricante, 
	  clarion.est_subgrupo_produto, 
	  clarion.est_grupo_produto, 
	  clarion.est_unidade_medida, 
	  clarion.com_cliente, 
	  clarion.fat_nota_fiscal_saida,
	  clarion.com_vendedor
	WHERE 
	  fat_movimentacao_itens_saida.id_produto = est_produto.id_produto AND
	  fat_movimentacao_itens_saida.id_nota_fiscal_saida = fat_nota_fiscal_saida.id_nota_fiscal_saida AND
	  est_produto.id_unidade_medida = est_unidade_medida.id_unidade_medida AND
	  est_produto.id_subgrupo_produto = est_subgrupo_produto.id_subgrupo_produto AND
	  est_fabricante.id_fabricante = est_produto.id_fabricante AND
	  est_subgrupo_produto.id_grupo_produto = est_grupo_produto.id_grupo_produto AND
	  com_cliente.id_pessoa = pcli.id_pessoa AND	  
	  fat_nota_fiscal_saida.id_natureza_operacao = natureza.id_natureza_operacao AND
	  fat_nota_fiscal_saida.id_cliente = com_cliente.id_cliente AND
	  fat_nota_fiscal_saida.id_vendedor = com_vendedor.id_vendedor AND
	   com_vendedor.id_pessoa = pvend.id_pessoa AND
	  CASE WHEN 'TRUE' = 'TRUE' THEN
		  extract (year from fat_nota_fiscal_saida.data_movimentacao) = 2010
	  END AND
	  CASE WHEN 'TRUE' = 'TRUE' THEN
		 com_vendedor.id_vendedor = com_vendedor.id_vendedor
	  END AND
	  CASE WHEN 'TRUE' = 'TRUE' THEN
		 com_cliente.id_cliente = com_cliente.id_cliente					
	  END AND
	  CASE WHEN 'TRUE' = 'TRUE' THEN
	        est_fabricante.id_fabricante = est_fabricante.id_fabricante
	  END AND
	  CASE WHEN 'TRUE' = 'TRUE' THEN
                est_grupo_produto.id_grupo_produto = est_grupo_produto.id_grupo_produto
	  END AND
	  CASE WHEN 'TRUE' = 'TRUE' THEN
                est_subgrupo_produto.id_subgrupo_produto = est_subgrupo_produto.id_subgrupo_produto
	  END
	  GROUP BY  est_unidade_medida.sigla, 
	  est_produto.codigo_produto, 
	  est_produto.descricao, 
	  est_produto.complemento, 
	  est_produto.embalagem_transporte, 
	  fat_movimentacao_itens_saida.quantidade_produto, 
	  est_unidade_medida.sigla, 
	  est_fabricante.nome,
	  fat_nota_fiscal_saida.data_movimentacao,
	  natureza.venda,
	  est_fabricante.id_fabricante,
	  com_cliente.id_cliente,
	  est_grupo_produto.id_grupo_produto,
	  com_vendedor.id_vendedor,
	  pcli.nome,
          pvend.nome,
	  est_subgrupo_produto.id_subgrupo_produto	  
	  ) r
	  GROUP BY
	  r.sigla, 
	  r.codigo_produto, 
	  r.descricao, 
	  r.complemento, 
	  r.embalagem_transporte, 
	  r.quantidade_produto, 	  
	  r.fabriNome,
	  r.cliNome,
	  r.vendNome,
	  r.ano;
#2

Acho que você consegue o numero de meses com venda assim:

(case when (SUM(r.soma1) > 0) THEN 1 ELSE 0 END) + case when (SUM(r.soma2) > 0) THEN 1 ELSE 0 END) + case when (SUM(r.soma3) > 0) THEN 1 ELSE 0 END) + ...etc ) AS NUMERO_DE_MESES_COM_VENDA
Ou já direto o resultado:

(SUM(r.soma1 + r.soma2 + r.soma3 + r.soma4 + r.soma5 + r.soma6 ...etc ) / (case when (SUM(r.soma1) > 0) THEN 1 ELSE 0 END) + case when (SUM(r.soma2) > 0) THEN 1 ELSE 0 END) + case when (SUM(r.soma3) > 0) THEN 1 ELSE 0 END) + ...etc )
É bom testar se tem pelo menos um mês com venda, senão vai dar divisão por Zero.

#3

jamir, tu és o cara, funcion certinho muito obrigado.
ai vai o código para quem tiver duvida parecida:

SELECT 
 r.sigla, 
   r.codigo_produto, 
   r.descricao, 
   r.complemento, 
   r.embalagem_transporte, 
   r.quantidade_produto,    
   r.fabriNome,
   r.cliNome,
   r.vendNome,
   r.ano,
   SUM(r.soma1) as jan,
   SUM(r.soma2) as fev,
   SUM(r.soma3) as mar,
   SUM(r.soma4) as abr,
   SUM(r.soma5) as mai,
   SUM(r.soma6) as jun,
   SUM(r.soma7) as jul,
   SUM(r.soma8) as ago,
   SUM(r.soma9) as sete,
   SUM(r.soma10) as outu,
   SUM(r.soma11) as nov,
   SUM(r.soma12) as dez,
  (SUM(r.soma1 + r.soma2 + r.soma3 + r.soma4 + r.soma5 + r.soma6 + r.soma7 + r.soma8 + r.soma9 + r.soma10 + r.soma11 + r.soma12) /  
  CASE WHEN((case when (SUM(r.soma1) > 0) THEN 1 ELSE 0 END) +  
         (case when (SUM(r.soma2) > 0) THEN 1 ELSE 0 END) +    
	  (case when (SUM(r.soma3) > 0) THEN 1 ELSE 0 END) +  
	  (case when (SUM(r.soma4) > 0) THEN 1 ELSE 0 END) +  
	  (case when (SUM(r.soma5) > 0) THEN 1 ELSE 0 END) +  
	  (case when (SUM(r.soma6) > 0) THEN 1 ELSE 0 END) +  
	  (case when (SUM(r.soma7) > 0) THEN 1 ELSE 0 END) +  
	  (case when (SUM(r.soma8) > 0) THEN 1 ELSE 0 END) +  
	  (case when (SUM(r.soma9) > 0) THEN 1 ELSE 0 END) +  
	  (case when (SUM(r.soma10) > 0) THEN 1 ELSE 0 END) +  
	  (case when (SUM(r.soma11) > 0) THEN 1 ELSE 0 END) +  
	  (case when (SUM(r.soma12) > 0) THEN 1 ELSE 0 END)     
    ) = 0 THEN 1 ELSE (
	   (case when (SUM(r.soma1) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma2) > 0) THEN 1 ELSE 0 END) +    
	   (case when (SUM(r.soma3) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma4) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma5) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma6) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma7) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma8) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma9) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma10) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma11) > 0) THEN 1 ELSE 0 END) +  
	   (case when (SUM(r.soma12) > 0) THEN 1 ELSE 0 END)
  )
   END) as mediaPonderada  

FROM (   
 SELECT 
   pcli.nome as cliNome,
          pvend.nome as vendNome,
   est_unidade_medida.sigla, 
   est_produto.codigo_produto, 
   est_produto.descricao,
   coalesce(est_produto.complemento, '') as complemento, 
   coalesce(est_produto.embalagem_transporte, '') as embalagem_transporte,
   fat_movimentacao_itens_saida.quantidade_produto,    
   est_fabricante.nome as fabriNome,
   est_fabricante.id_fabricante,
   com_cliente.id_cliente,
   est_grupo_produto.id_grupo_produto,
   com_vendedor.id_vendedor,
   est_subgrupo_produto.id_subgrupo_produto,
   extract (year from fat_nota_fiscal_saida.data_movimentacao) as ano,   
   
   
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 1 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma1,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 2 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma2,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 3 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma3,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 4 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma4,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 5 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma5,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 6 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma6,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 7 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma7,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 8 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma8,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 9 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma9,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 10 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma10,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 11 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma11,
    CASE WHEN extract (month from fat_nota_fiscal_saida.data_movimentacao) = 12 AND natureza.venda ='TRUE' THEN SUM(fat_movimentacao_itens_saida.quantidade_produto) ELSE 0 END as soma12  
   
 FROM 
   clarion.fat_movimentacao_itens_saida, 
   clarion.com_pessoa as pcli, 
   clarion.com_pessoa as pvend, 
   clarion.com_natureza_operacao natureza, 
   clarion.est_produto, 
   clarion.est_fabricante, 
   clarion.est_subgrupo_produto, 
   clarion.est_grupo_produto, 
   clarion.est_unidade_medida, 
   clarion.com_cliente, 
   clarion.fat_nota_fiscal_saida,
   clarion.com_vendedor
 WHERE 
   fat_movimentacao_itens_saida.id_produto = est_produto.id_produto AND
   fat_movimentacao_itens_saida.id_nota_fiscal_saida = fat_nota_fiscal_saida.id_nota_fiscal_saida AND
   est_produto.id_unidade_medida = est_unidade_medida.id_unidade_medida AND
   est_produto.id_subgrupo_produto = est_subgrupo_produto.id_subgrupo_produto AND
   est_fabricante.id_fabricante = est_produto.id_fabricante AND
   est_subgrupo_produto.id_grupo_produto = est_grupo_produto.id_grupo_produto AND
   com_cliente.id_pessoa = pcli.id_pessoa AND   
   fat_nota_fiscal_saida.id_natureza_operacao = natureza.id_natureza_operacao AND
   fat_nota_fiscal_saida.id_cliente = com_cliente.id_cliente AND
   fat_nota_fiscal_saida.id_vendedor = com_vendedor.id_vendedor AND
    com_vendedor.id_pessoa = pvend.id_pessoa AND
   CASE WHEN 'TRUE' = 'TRUE' THEN
    extract (year from fat_nota_fiscal_saida.data_movimentacao) = 2010
   END AND
   CASE WHEN 'TRUE' = 'TRUE' THEN
   com_vendedor.id_vendedor = com_vendedor.id_vendedor
   END AND
   CASE WHEN 'TRUE' = 'TRUE' THEN
   com_cliente.id_cliente = com_cliente.id_cliente     
   END AND
   CASE WHEN 'TRUE' = 'TRUE' THEN
         est_fabricante.id_fabricante = est_fabricante.id_fabricante
   END AND
   CASE WHEN 'TRUE' = 'TRUE' THEN
                est_grupo_produto.id_grupo_produto = est_grupo_produto.id_grupo_produto
   END AND
   CASE WHEN 'TRUE' = 'TRUE' THEN
                est_subgrupo_produto.id_subgrupo_produto = est_subgrupo_produto.id_subgrupo_produto
   END
   GROUP BY  est_unidade_medida.sigla, 
   est_produto.codigo_produto, 
   est_produto.descricao, 
   est_produto.complemento, 
   est_produto.embalagem_transporte, 
   fat_movimentacao_itens_saida.quantidade_produto, 
   est_unidade_medida.sigla, 
   est_fabricante.nome,
   fat_nota_fiscal_saida.data_movimentacao,
   natureza.venda,
   est_fabricante.id_fabricante,
   com_cliente.id_cliente,
   est_grupo_produto.id_grupo_produto,
   com_vendedor.id_vendedor,
   pcli.nome,
          pvend.nome,
   est_subgrupo_produto.id_subgrupo_produto   
   ) r
   GROUP BY
   r.sigla, 
   r.codigo_produto, 
   r.descricao, 
   r.complemento, 
   r.embalagem_transporte, 
   r.quantidade_produto,    
   r.fabriNome,
   r.cliNome,
   r.vendNome,
   r.ano;
#4