Desde a primeira versão, o padrão incorpora funções classificadas como "agregação". São funções nativas de consulta que servem para agrupar informações, agregando numa única linha o conteúdo de vários registros. Dessa forma, os detalhes da informação original são postos de lado e as informações são tratadas em conjunto.
Atualmente, as funções de agregação disponíveis no SQL são as seguintes: AVG (calcula a média dos valores de um campo determinado), COUNT (retorna a quantidade de registros existentes), SUM (calcula a soma dos valores de um determinado campo), MAX (retorna o maior valor existente para um campo especificado) e MIN (retorna o menor valor existente para um campo especificado). Todas essas funções são aplicadas apenas aos registros que atenderem os critérios estabelecidos na cláusula WHERE da query onde estiverem sendo usadas.
Pode parecer confuso a princípio mas o conceito de agregação é, na verdade, bastante simples. Considere o seguinte conjunto de registros existente numa tabela no banco de dados:
| NF | Produto | Cliente | Qtde | Valor | Dia |
| 1 | P001 | C1 | 8 | R$ 160,00 | 5 |
| 1 | P002 | C1 | 2 | R$ 34,00 | 5 |
| 1 | P003 | C1 | 1 | R$ 58,00 | 5 |
| 2 | P002 | C3 | 20 | R$ 340,00 | 7 |
| 3 | P001 | C2 | 12 | R$ 240,00 | 8 |
| 3 | P002 | C2 | 5 | R$ 85,00 | 8 |
| 4 | P001 | C1 | 2 | R$ 40,00 | 10 |
| 5 | P003 | C3 | 4 | R$ 232,00 | 15 |
Um exemplo simples de agregação com esses registros seria somar a coluna Valor deles todos. Supondo que a tabela se chame TABELA, a query e seu retorno seriam:
SELECT SUM (Valor) FROM TABELA
Repare que apenas um registro é retornado. É por isso que eu disse que as agregações escondem os detalhes dos registros envolvidos. O uso de agregações não impede que se aplique a cláusula WHERE na query, isto é, ainda posso restringir quais registros eu quero incluir na agregação. O exemplo da soma considerando apenas as notas do cliente C1 ficaria:------------------
1189,00
1189,00
SELECT SUM (Valor) FROM TABELA
WHERE Cliente = 'C1'
Na maioria dos casos, restringir tanto os registros afetados e o retorno não é desejável. Numa aplicação real, quase sempre as informações terão que vir um pouco mais detalhadas. Pode ser necessário, por exemplo, trazer as somas de cada cliente separadamente, sem que se tenha que especificar um cliente em particular. Esse efeito é obtido agrupando-se os registros através da cláusula GROUP BY.WHERE Cliente = 'C1'
------------------
292,00
292,00
SELECT Cliente, SUM (Valor), COUNT(*) AS QtdeReg
FROM TABELA
GROUP BY Cliente
Acrescentei no quadro anterior também uma coluna que mostra quantos registros da tabela foram considerados em cada linha trazida, isto é, quantos registros estão agregados em cada linha. Isso exemplifica o uso da função COUNT.FROM TABELA
GROUP BY Cliente
| Cliente | QtdeReg | |
| ------------------ | ------------------ | ------------------ |
| C1 | 292,00 | 4 |
| C2 | 325,00 | 2 |
| C3 | 572,00 | 2 |
Se for preciso, é permitido detalhar ainda mais as informações adicionando novos agrupamentos - o produto ou o dia da movimentação, por exemplo. Além disso, todos os elementos de uma query comum podem ser usados, tais como o WHERE (já mostrado) e o ORDER BY (para que as linhas retornadas estejam classificadas numa determinada ordem).
A cláusula WHERE determina quais registros da tabela devem ser incluídos na agregação. E se você quiser restringir as linhas retornadas, trazendo apenas aquelas que atingirem determinado valor agregado ? Suponha, por exemplo, que na query do quadro anterior você queria exibir apenas os clientes cuja soma de valor seja superior a 300. Para esta situação, o padrão SQL define a cláusula HAVING que nada mais é que um WHERE aplicado ao resultado da agregação. Veja a implementação do exemplo:
SELECT Cliente, SUM (Valor), COUNT(*) AS QtdeReg
FROM TABELA
GROUP BY Cliente
HAVING (SUM (Valor) > 300) AND (COUNT(*) >= 1)
Repare que o HAVING é aplicado após o agrupamento (GROUP BY) enquanto o WHERE é aplicado ao SELECT. O exemplo também mostra que é possível combinar mais de um valor de agregação no HAVING para obter filtros mais complexos.FROM TABELA
GROUP BY Cliente
HAVING (SUM (Valor) > 300) AND (COUNT(*) >= 1)
| Cliente | QtdeReg | |
| ------------------ | ------------------ | ------------------ |
| C2 | 325,00 | 2 |
| C3 | 572,00 | 2 |
24 comentários :
Parebens djow :D
tiro umas duvidas que eu tinha.
Digamos que eu queira extrair dados que estão em tenha 4 tabelas e da 5a eu quero apenas a soma de um determinado campo que relaciona com uma das outras pelo código. Como fazer isso?
Ao envolver mais de uma tabela em uma agregação aja como se cada linha resultante do seu JOIN fosse um registro de uma única tabela.
Ou seja, construa a cláusula WHERE normalmente, estipulando o JOIN entre as tabelas. Na cláusula SELECT, inclua os campos que deseja sumarizar ou agrupar, não importando em qual tabela o campo está. Não se esqueça, porém, que os campos do SELECT nos quais não for aplicada uma agregação devem constar da cláusula GROUP BY.
Um exemplo com tabelas e campos hipotéticos, onde a soma dos valores de Pedidos são agrupados por Cliente e pelo Estado onde a mercadoria será entregue :
SELECT C.NomeCliente, E.NomeEstado, SUM (P.Valor), COUNT(P.*)
FROM CLIENTE C, PEDIDOS P, ENDERECOS E
WHERE C.CodCliente = P.CodCliente
P.CodEnderecoEntrega = E.CodEndereco
GROUP BY C.NomeCliente, E.NomeEstado
E SE EU QUISER FILTRAR AINDA POR DATA ?
Novos filtros podem ser acrescentados sem problemas na cláusula WHERE - ela serve apenas para restringir os dados que serão considerados pela agregação. No entanto, se o objetivo é também agrupar pela data, o campo correspondente deve ser obrigatoriamente incluído tanto no SELECT quanto no GROUP BY.
Gostei muito do seu post,e me ajudou muito.
como faço pra somar duas queries ou buscar duas palavras ao mesmo tempo em uma tabela?
Obrigado
Imagino que por "somar duas queries" você queira dizer "trazer de uma única vez o resultado de duas queries". Se for isso, use uma UNION ligando ambas as queries. Usar esse comando exige que as queries envolvidas tragam o mesmo número de colunas (campos) e essas colunas devem ter tipos de dados conversíveis entre si para funcionar. Veja uma documentação aqui.
Quanto a buscar palavras ao mesmo tempo, use a cláusula WHERE da sua query. Para isso, construa as comparações isoladamente e as ligue com o operador lógico OR. Exemplo:
SELECT * FROM MINHA_TABELA
WHERE (CAMPOTEXTO = '%PALAVRA%'
OR CAMPOTEXTO = '%OUTRA%')
Bom dia,
Se eu quisesse fazer a SOMA de vendas de n produtos e que devolvesse apenas o MAX sem repetir os produtos. Ex.:
Material Metros Cliente Final Nome
TC223300013 142.423 STORES LLC MOVIMIENTO
TC223300013 288.520 GESIS MOVIMIENTO
TC223300013 162.559 D. T. SA MOVIMIENTO
TC086200009 42.816 D. T. SA MARC
TC222200009 25.257 GMBH LIBERTAD
TC223300667 61.609 D. T. SA MOVIMIENTO
TC179100012 63.175 D. T. SA TOUCHE
Queria que devolvesse o max de cada produto, indicando o cliente sem repetir outra vez o produto.
Obrigado
Marco
Vc pode montar uma sub-query para retornar o cliente filtrando-a pelo produto e pela própria qtde:
select max (M.Metros), M.material,
(select (M2.cliente)
from teste M2
where M2.Material = M.material
and M2.metros = max (M.Metros)
) As Cliente
from teste M
group by Material
Isso pode gerar um erro caso exista mais de um cliente com a mesma qtde. Nessa situação, restrinja o retorno da sub-query a um único registro com o TOP.
[]s
[]
Bom dia Eng. Luís,
Obrigado pela resposta, no entanto, dentro da minha query não consigo estruturar conforme o seu apontamento. Se não for abusar muito da sua pessoa, como faço na minha query:
SELECT
`201210-Vendas`.Material as Material,
`201210-Vendas`.Nome,
`201210-Vendas`.`Cliente Final`,
Sum (`BW: QTD UMB`) as Metros
FROM `201210-Vendas` `201210-Vendas`
WHERE (`201210-Vendas`.Material Like 'TC%') and `201210-Vendas`.`Canal distribuição` IN ('01', '02')
group by `201210-Vendas`.`Cliente Final`, `201210-Vendas`.Nome, `201210-Vendas`.Material
having sum(`BW: QTD UMB`) >=10
Order by sum(`BW: QTD UMB`) desc
Esta query apenas dá soma do material, repetindo por clientes...
É possível fazer o seu apontamento usando as minhas fontes?
Sou licenciado em contabilidade e há querys que ainda não consigo compreender....
Obrigado pela ajuda.
Marco Azevedo
Marco
Suponho que vc queira listar a soma das qtdes vendidas para cada material e informar por material o nome do cliente que mais comprou. Adaptando sua query, seria algo assim :
SELECT
`201210-Vendas`.Material as Material,
`201210-Vendas`.Nome,
(SELECT `201210-Vendas2`.`Cliente Final`,
FROM `201210-Vendas` `201210-Vendas2`
WHERE (`201210-Vendas2`.Material Like 'TC%') -- mesmo filtro da query externa
AND `201210-Vendas2`.`Canal distribuição` IN ('01', '02') -- mesmo filtro da query externa
AND `201210-Vendas2`.Material = `201210-Vendas`.Material -- filtra o material atual
AND MAX (`201210-Vendas2`.`BW: QTD UMB`) = `201210-Vendas2`.`BW: QTD UMB`) -- filtra apenas o registro com a maior Qtde
AS `Cliente Final`,
Sum (`BW: QTD UMB`) as Metros
FROM `201210-Vendas` `201210-Vendas`
WHERE (`201210-Vendas`.Material Like 'TC%') and `201210-Vendas`.`Canal distribuição` IN ('01', '02')
group by `201210-Vendas`.Nome, `201210-Vendas`.Material
having sum(`BW: QTD UMB`) >=10
Order by sum(`BW: QTD UMB`) desc
Não sei que ferramenta vc está usando para executar sua query nem em que banco de dados ela está operando. A sintaxe que sugeri se aplica ao SQL Server e a outros bancos de mercado.
[]
Boa tarde
Antes demais queria agradecer a amabilidade de me ajudar a construir esta query. No entanto, a query deu um erro que passo a transcrever:
"Não pode ter uma função de agregaçãona clausula WHERE (`201210-Vendas2`.Material Like 'TC%')AND `201210-Vendas2`.`Canal distribuição` IN ('01', '02') AND `201210-Vendas2`.Material = `201210-Vendas`.Material AND MAX (`201210-Vendas2`.`BW: QTD UMB`) = `201210-Vendas2`.`BW: QTD UMB`".
Agradeço imenso e, se não puder me ajudar euy compreendo e vou tentar analisar a lógica desta construção e ver o que poderá estar a dar erro.
Muito obrigado
Marco Azevedo
(Os dados são retirados do SAP para uma base de dados em acess 2010, e escrevo o código em SQL dentro do excel).
Pela mensagem reportada, o executor de query do Excel não admite a sintaxe sugerida. Neste cenário, uma solução é montar 2 queries separadas: uma para calcular a soma e outra para computar o cliente que mais vendeu.
[]s
Boa tarde Sr. Eng. Luís,
Depois de muita leitura sobre o assunto, encontrei a solução para o meu problema. Mesmo assim muito obrigado porque foi com a sua resposta e com alguns manuais que encontrei a solução. Deixo aqui a instrução em SQL:
Select
S1.Material, S1.Nome, S1.Cliente, TopMaterial.Metros
From
(SELECT
`201210-Vendas`.Material as Material,
`201210-Vendas`.Nome as Nome,
`201210-Vendas`.`Cliente Final` As Cliente,
Sum (`BW: QTD UMB`) as Metros
FROM `201210-Vendas` `201210-Vendas`
WHERE (`201210-Vendas`.Material Like 'TC%') and `201210-Vendas`.`Canal distribuição` IN ('01', '02')
group by `201210-Vendas`.`Cliente Final`, `201210-Vendas`.Nome, `201210-Vendas`.Material
having sum(`BW: QTD UMB`) >=10) as S1
INNER JOIN
(SELECT
S2.Material,
S2.Nome,
Max(S2.Metros) as Metros
From
(SELECT
`201210-Vendas`.Material as Material,
`201210-Vendas`.Nome as Nome,
`201210-Vendas`.`Cliente Final` As Cliente,
Sum (`BW: QTD UMB`) as Metros
FROM `201210-Vendas` `201210-Vendas`
WHERE (`201210-Vendas`.Material Like 'TC%') and `201210-Vendas`.`Canal distribuição` IN ('01', '02')
group by `201210-Vendas`.`Cliente Final`, `201210-Vendas`.Nome, `201210-Vendas`.Material
having sum(`BW: QTD UMB`) >=10) as S2
group by S2.Material, s2.Nome) As TopMaterial
ON S1.Material=Topmaterial.material and S1.metros =TopMaterial.metros
order by TopMaterial.Metros desc
Um abraço e muito obrigado
Bom dia Sr. Eng. Luís,
Em SQL é possível juntar várias subquery's para chamar relartórios para dar por artigo vendido, o nº clientes por artigo, O max de vendas desse artigo, as Vendas totais desse artigo e a última data de venda.
Tenho 4 query´s para dar isto tudo...é possível agregar as quatro?
1) Nº Clintes e o Máximo/Cliente desse Artigo.
Select
S1.Nome,
S1.Material,
S3.Clientes,
S3.[Vendas Metros] as Max_Cliente
from
(SELECT
Vendas.`Cliente Final` as Clientes,
Vendas.Material as Material,
Vendas.Nome as Nome,
sum(Vendas.`BW: QTD UMB`) as [Vendas Metros]
FROM Vendas
Where [Canal distribuição] in ('01', '02') and [Data Fatura] between #01-01-2011# and #31-12-2012#
Group BY Vendas.`Cliente Final` , Vendas.Material, Vendas.Nome
Having sum(Vendas.`BW: QTD UMB`) >1000 ) as S1
INNER JOIN
(Select
Distinct
S2.Material,
Count(S2.Clientes) as Clientes,
Max(s2.[Vendas Metros]) as [Vendas Metros]
from
(SELECT
Vendas.`Cliente Final` as Clientes,
Vendas.Material as Material,
Vendas.Nome as Nome,
sum(Vendas.`BW: QTD UMB`) as [Vendas Metros]
FROM Vendas
Where [Canal distribuição] in ('01', '02') and [Data Fatura] between #01-01-2011# and #31-12-2012#
Group BY Vendas.`Cliente Final`, Vendas.Material, Vendas.Nome
Having sum(Vendas.`BW: QTD UMB`) >1000) as S2
Group by S2.Material) as S3
on s1.Material=s3.material and s1.[Vendas Metros]=s3.[Vendas Metros]
Order by S3.[Vendas Metros] desc
2-Vendas Totais por Artigo
SELECT
Vendas.Material,
Vendas.Nome,
sum(Vendas.`BW: QTD UMB`) as V_Total
FROM Vendas
Where [Canal distribuição] in ('01','02') and [Data Fatura] between #01-01-2011# and #31-12-2012#
Group By Vendas.Material, Vendas.Nome
Having sum(Vendas.`BW: QTD UMB`) >1000
Order By sum(Vendas.`BW: QTD UMB`) desc
3-Última data de Venda por artigo
SELECT
Vendas.Material as Material,
max(Vendas.`Data Fatura`) as Data
FROM Vendas Vendas
Where [Canal Distribuição] in ('01', '02')
Group by Vendas.Material
4-Top Clintes por artigo
Select
S1.Nome,
S1.Material,
S1.Clientes,
S3.[Vendas Metros]
from
(SELECT
Vendas.`Cliente Final` as Clientes,
Vendas.Material as Material,
Vendas.Nome as Nome,
sum(Vendas.`BW: QTD UMB`) as [Vendas Metros]
FROM Vendas
Where [Canal distribuição] in ('01', '02') and [Data Fatura] between #01-01-2011# and #31-12-2012#
Group BY Vendas.`Cliente Final` , Vendas.Material, Vendas.Nome
Having sum(Vendas.`BW: QTD UMB`) >1000 ) as S1
INNER JOIN
(Select
S2.Material,
max(S2.Clientes) As Clientes,
max(S2.[Vendas Metros]) as [Vendas Metros]
from
(SELECT
Vendas.`Cliente Final` as Clientes,
Vendas.Material as Material,
Vendas.Nome as Nome,
sum(Vendas.`BW: QTD UMB`) as [Vendas Metros]
FROM Vendas
Where [Canal distribuição] in ('01', '02') and [Data Fatura] between #01-01-2011# and #31-12-2012#
Group BY Vendas.`Cliente Final`, Vendas.Material, Vendas.Nome
Having sum(Vendas.`BW: QTD UMB`) >1000) as S2
Group by S2.Material) as S3
on s1.Material=s3.material and s3.[Vendas Metros]=s1.[Vendas Metros]
Order by S3.[Vendas Metros] desc
Ainda não vi nenhum exmplo para aprender a lógica de fazer subquery´s sob subquery´s....
Se me puder ajudar ou indicar algum manula ou site para estudar, agradecia imenso.
Obrigado
Marco Azevedo
Marco
Acredito que na maioria dos SGBD você não conseguirá aplicar uma agregação (como o max) diretamente no resultado de uma UNION. Você terá primeiro que gravar esse resultado numa tabela (mesmo que temporária) e então submeter nela uma query que faça a agregação desejada.
[]s
Bom dia Eng. Luís,
Existe alguma diferença no SQL entre chamar uma variável para o utilizador colocar dados e fixar a variavel dentro da instrução?
O caso é este:
Transform Sum(s1.Consumo) as Peso
Select
S1.Material,
QRamas.`Texto breve material` as Nome
From
(SELECT
MB51Cons.Material as Material,
Year(MB51Cons.Dtlçto) as Ano,
sum(MB51Cons.`Qtd UM registro`)*'-1' as Consumo
FROM MB51Cons MB51Cons
Where Year(MB51Cons.Dtlçto)>=2011 and Month (MB51Cons.Dtlçto) <=2
Group By MB51Cons.Material, Year(MB51Cons.Dtlçto)) as S1
Inner Join QRamas ON S1.Material=QRamas.Material
Group By S1.Material,
QRamas.`Texto breve material`
Pivot ano
Aqui nesta parte o SQL baralha-se " Where Year(MB51Cons.Dtlçto)>=2011 and Month (MB51Cons.Dtlçto) <=[?]
"
Se fixar o mês "Month (MB51Cons.Dtlçto) <= 2 " o resultado da query sai na perfeição; se chamar a variável para o utilizador escolher o mês, sai dados disparatados, ou seja, se escolher <=3, os somatórios aparecem Janeiro, Fevereiro, Março, Outubro, Novembro e Dezembro.
Há alguma razão para isto?
Obrigado
Marco Azevedo
Marco
Usar variáveis na query ou fixar o valor não deve fazer diferença quanto aos registros retornados. O que parece estar acontecendo é que sua variável simplesmente não está sendo levada em conta.
Como está sendo feita a passagem do valor informado pelo utilizador para a variável na query ? Que ferramenta está usando ?
[]s
Bom dia,
Estou usar o excel 2010 no item parâmetros. Não estou conseguindo perceber a razão de resultados diferentes quando a váriavel é fixada na instruçõe e quando a variável é digitada pelo utilizador.
Marco Azevedo
Marco
Se a planilha não possuir informações confidenciais, envie-a para o email do blog para eu dar uma olhada.
[]s
Como faço usando o seu exemplo para a consulta me trazer todos dados, porém, agrupando por cliente, e abaixo de cada grupo incluir uma linha mostrando a soma da coluna Valor.
Como você me falou que usando as agregações é possível detalher muito mais do que o que você fez em seu exemplo gostaria de saber se isso é possível, e se for, como seria, por que isso seria exatamente o que estou precisando.
Darlan
Alterar o grau de detalhamento da agregação significa incluir ou remover campos no agrupamento. Quanto mais campos houverem, mais detalhado será o resultado.
Para sua necessidade, pelo que entendi, você quer ter tanto os detalhes quanto o resumo. Para isso, serão necessárias duas queries separadas postas juntas através de uma UNION. Um campo fixo deve diferenciar a query analítica da sintética e o conjunto todo deve ser ordenado por Cliente e por esse campo fixo.
Exemplificando com a tabela declarada no post teríamos algo como:
SELECT 1 As Tipo, Cliente, Valor
FROM TABELA
UNION
SELECT 2 As Tipo, Cliente, SUM (Valor)
FROM TABELA
GROUP BY Cliente
ORDER BY Cliente, 1
Os registros com tipo 1 serão analíticos enquanto os de tipo 2 trazem a soma por cliente. A ordenação garante que a linha com a soma seja listada imediatamente após as linhas detalhadas do Cliente.
[]s
Obrigado, pelo retorno, mas caso eu queira implementar alguma condição WHERE, isso é possível, e se no meu select eu precisar de mais de uma tabela que esteja relacionadas por chaves primária e índices, também é possível?
Como são 2 queries independentes, vc pode usar qualquer recurso SQL nelas - incluindo joins e filtros. A única exigência é quanto aos campos no SELECT de ambas: devem ter a mesma qtde de campos e eles devem ser do mesmo tipo.
OBS: no caso do WHERE, só faz sentido para o seu cenário se as queries considerarem exatamente o mesmo conjunto de dados. Ou seja, o WHERE deve ser igual.
[]s
Postar um comentário
OBS: Os comentários enviados a este Blog são submetidos a moderação. Por isso, eles serão publicados somente após aprovação.
Observação: somente um membro deste blog pode postar um comentário.