Média de quantidades mensal e anual

[Geelherme 1]

Olá amigos.. preciso de um SQL que me retorne a média por mês e por ano de um determinado produto.

Possuo duas tabelas chamadas `cfop`e `vendas` que se relacionam entre si através do campo CFOP. A tabela CFOP possui duas colunas (CFOP e DESCRICAO) e na minha consulta deve retornar apenas os registros cuja descricao de cfop comecem com a string 'VEND'.

No exemplo abaixo, quero fazer a média apenas do mês de janeiro para comparar com os resultados de uma planilha que possuo, por isso tantos filtros. Se funcionar para esse mês, vai funcionar para os demais também. Existem 4 registros como vou listar agora com apenas os campos necessários para o entendimento do problema:

 

EMPRESA : 1, 1, 2, 2, 2

QTD : 1, 3, 15, 1, 1

REFERENCIA : 16911, 16911, 16911, 16911, 16911

 

Com a query abaixo, os valores de SUM(QTD) não me retornam 4, 17 como eu esperava. Em vez disso, surge 50, 112. Sem o join essa quantidade diminui um pouco. Coloquei o disntinct no começo mas não mudou nada, alguém pode dar um help??? Tentei usar COUNT (QTD) tbm, mas não funcionou. Como obter a soma da quantidade dessas vendas agrupadas por empresa e retornando o valor desejado??

valeu!

 

SELECT DISTINCT SUM(QTD), vendas.* FROM vendas 
INNER JOIN cfop ON (vendas.CFOP = cfop.CFOP AND cfop.DESCRICAO LIKE 'VEND%')
WHERE vendas.REFERENCIA = '16911' AND YEAR(vendas.DT_EMISSAO) = 2010 AND MONTH(vendas.DT_EMISSAO) = 1
GROUP BY EMPRESA

[Motta 645]

Algo assim :

 

SELECT YEAR(vendas.DT_EMISSAO),avg(QTD)
FROM vendas 
INNER JOIN cfop ON (vendas.CFOP = cfop.CFOP AND cfop.DESCRICAO LIKE 'VEND%')
WHERE vendas.REFERENCIA = '16911' AND YEAR(vendas.DT_EMISSAO) = 2010 
GROUP BY YEAR(vendas.DT_EMISSAO)

[Geelherme 1]

Obrigado pela resposta mas não funcionou corretamente. Tentei modificar o que fizeste, mas ainda assim o banco me retorna muito mais valores do que o desejado. Quando eu faço assim:

 

SELECT DISTINCT EMPRESA, QTD

FROM vendas

INNER JOIN cfop ON (vendas.CFOP = cfop.CFOP)

WHERE vendas.REFERENCIA = '16911' AND YEAR(vendas.DT_EMISSAO) = 2010 AND MONTH(DT_EMISSAO) = 1 AND cfop.DESCRICAO LIKE 'VEND%'

 

ele me mostra os dois resultados corretos, tipo:

EMPRESA: 1, 1

QTD: 1, 3

 

A soma das quantidades então deveria retornar 4, mas quando eu mudo o QTD do SELECT acima para SUM(QTD), ele me aparece com 112. Alguém sabe o que pode ser?

 

_____________________EDIT_____________________

Pode ser resolvido usando SUM(DISTINCT QTD)

 

Obrigado por quem tentou ajudar ^^

[Motta 645]

Talvez a query tenha um produto cartesiano indevido.

 

Faça a query sem agrupamento até obter o resultado correto.

Faça o agrupamento AVG (média).

[Geelherme 1]

Hum, ok... aconteceu de fato um resultado indevido.

Minha query atualmente está assim:

 

SELECT DISTINCT NF, EMPRESA, QTD
FROM vendas 
INNER JOIN cfop ON (vendas.CFOP = cfop.CFOP)
WHERE 

vendas.NM_CLIENTE NOT LIKE 'PARTNERS' AND vendas.NM_CLIENTE NOT LIKE 'HOSPICARE' AND 
vendas.NM_CLIENTE NOT LIKE 'FLEX%LAB' AND vendas.NM_CLIENTE NOT LIKE 'DARTNER'
AND vendas.REFERENCIA = '16911' AND YEAR(vendas.DT_EMISSAO) = 2010 
AND MONTH(DT_EMISSAO) = 1 AND cfop.DESCRICAO LIKE 'VEND%'

ORDER BY EMPRESA DESC

e o retorno é:

NF 	EMPRESA  	QTD 
6186	Partners	1
6284	Partners	3
6733	Hospicare	1
6734	Hospicare	1
6423	Hospicare	15

Eu gostaria que o retorno fosse:

NF 	EMPRESA  	QTD 
6186	Partners	4
6423	Hospicare	17

Nesse caso, tentei usar o AVG mas não entendo bem como ele funciona. Dei uma lida até na sua descrição no site oficial, mes ele divide o total pela quantidade de registros, acho que não seria a mais adequada. Mas valeu pelo alerta, de fato havia um erro na query... se puder me ajudar mais uma vez, seria de extrema importância... valeu!

[Motta 645]

QTD é atributo de vendas ou de cfop ?

 

O relacionamento é só

vendas.CFOP = cfop.CFOP

?

Marretando :

 

select empresa,avg(qtd) as qtd
from
(
SELECT DISTINCT NF, EMPRESA, QTD
FROM vendas 
INNER JOIN cfop ON (vendas.CFOP = cfop.CFOP)
WHERE 
vendas.NM_CLIENTE NOT LIKE 'PARTNERS' AND vendas.NM_CLIENTE NOT LIKE 'HOSPICARE' AND 
vendas.NM_CLIENTE NOT LIKE 'FLEX%LAB' AND vendas.NM_CLIENTE NOT LIKE 'DARTNER'
AND vendas.REFERENCIA = '16911' AND YEAR(vendas.DT_EMISSAO) = 2010 
AND MONTH(DT_EMISSAO) = 1 AND cfop.DESCRICAO LIKE 'VEND%'
)
group by empresa

mas é melhor rever a query.

[Geelherme 1]

Hum, QTD é atributo de VENDAS e o relacionamento é só vendas.CFOP = cfop.CFOP

peguei a sua query e modifiquei os alias para poder executa-la. Ficou assim e obtive o resultado esperado:

select U.empresa, SUM(U.qtd) as qtd
from
(
SELECT DISTINCT V.NF, V.EMPRESA, V.QTD
FROM vendas V
INNER JOIN cfop C ON (V.CFOP = C.CFOP)
WHERE 
V.NM_CLIENTE NOT LIKE 'PARTNERS' AND V.NM_CLIENTE NOT LIKE 'HOSPICARE' AND 
V.NM_CLIENTE NOT LIKE 'FLEX%LAB' AND V.NM_CLIENTE NOT LIKE 'DARTNER'
AND V.REFERENCIA = '16911' AND YEAR(V.DT_EMISSAO) = 2010 
AND MONTH(V.DT_EMISSAO) = 1 AND C.DESCRICAO LIKE 'VEND%'
) AS U
group by U.empresa

Vou agora verificar o calculo das medias utilizando AVG() nos campos de valor. Muitíssimo obrigado campeão.

Vou ter que agora agrupar por Ano e Mes, vou dar uma olhada para ver o que consigo fazer.

 

Ok cara, ficou perfeito agora:

 

SELECT YEAR(U.DT_EMISSAO) AS ANO, MONTH(U.DT_EMISSAO) AS MES, U.EMPRESA, U.REFERENCIA, SUM(U.QTD) as QTD, SUM(U.VLR_UNIT) as VLR_UNIT, (SUM(U.VLR_UNIT)/SUM(U.QTD)) AS MEDIA
FROM
(
SELECT DISTINCT V.NF, V.EMPRESA, V.QTD, V.DT_EMISSAO, V.VLR_UNIT*V.QTD AS VLR_UNIT, REFERENCIA
FROM vendas V
INNER JOIN cfop C ON (V.CFOP = C.CFOP)
WHERE 
V.NM_CLIENTE NOT LIKE 'PARTNERS' AND V.NM_CLIENTE NOT LIKE 'HOSPICARE' AND 
V.NM_CLIENTE NOT LIKE 'FLEX%LAB' AND V.NM_CLIENTE NOT LIKE 'DARTNER'
AND C.DESCRICAO LIKE 'VEND%'
) AS U
GROUP BY ANO, MES, U.EMPRESA, U.REFERENCIA
ORDER BY ANO, MES

[Motta 645]

Beleza, mas uma coisa que sempre digo é :

 

Desconfie sempre de queries com DISTINCT, você pode estar escondendo um problema.