Query com agrupamento com CASE com sub-query dá erro... como resolver?

Question

Ola pessoal

Estou com uma dificuldade para estrutura uma consulta, onde preciso totais de faturamento por CFOP, mes, ano.

A consulta deve retornar essa estrutura:
Empresa  -  CFOP   -   Mes  -    Ano    -   Faturamento    -   FaturamentoDefensivos

Pensei em fazer assim:

SELECT nt.CodEmpresa, it.CFOP, MONTH(nt.DataEmissao) as Mes, YEAR(nt.DataEmissao) as Ano

, SUM(it.ValorTotal - it.DescontoValor)    as Faturamento
--até aqui tudo bem

, sum(case when it.CodProduto in (
    select cod from CD_Produto pro inner join Insumos_Grupos_Defensivos def
    on pro.Grupo = def.CodGrupo and pro.SubGrupo = def.CodSubGrupo and pro.Divisao = def.CodDivisao
    )
    then
        it.ValorTotal - it.DescontoValor
    else 0 end
  ) as FaturamentoDefensivos
 
  FROM Notas nt inner join ItensNotas it on nt.Seq = it.seq
INNER JOIN CD_CF_Natureza nat on nat.Codigo = nt.CodNaturezaOperacao
WHERE nt.CodNaturezaOperacao in (
    select codNatureza from CD_CF_Insumos_Naturezas
    )
GROUP BY nt.CodEmpresa, it.CFOP, MONTH(nt.DataEmissao), YEAR(nt.DataEmissao)

Enfim, num dos valores, preciso considerar apenas os produtos de Grupos que estão na tabela Insumos_Grupos_Defensivos

Mas dá o erro "Não é possível executar uma função de agregação em uma expressão que contenha um agregado ou uma subconsulta."

Não entendo porque não poderia ter uma subconsulta dentro do SUM... mas enfim

Como poderia resolver isso?

Obrigado!!

---

Julio C.

Answer 1

Boa tarde,

Experimente dessa forma:

with CTE_Def as
(
    select 
        pro.cod 
    from CD_Produto pro 
    inner join Insumos_Grupos_Defensivos def 
        on 
            pro.Grupo = def.CodGrupo and 
            pro.SubGrupo = def.CodSubGrupo and 
            pro.Divisao = def.CodDivisao
)

SELECT 
    -- ...
    sum
        (case when def.Cod is null 
             then 0 
             else it.ValorTotal - it.DescontoValor 
         end) as FaturamentoDefensivos
FROM Notas nt 
inner join ItensNotas it 
    on nt.Seq = it.seq
INNER JOIN CD_CF_Natureza nat 
    on nat.Codigo = nt.CodNaturezaOperacao
LEFT JOIN CTE_Def def
    on def.cod = it.CodProduto
-- ...

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 2

É isso aí mesmo! Obrigado!

eu estava pensando na CTE já, mas não conseguia visualizar como

deu certinho!

---

Julio C.

Answer 3

Uma curiosidade/duvida que eu tenho em relação às cte's:

É possivel usar mais do que uma CTE numa consulta?
Por exemplo, se no caso acima, eu tivesse necessidade de fazer o mesmo, mas com outras tabelas (para gerar outros campos)?

---

Julio C.

Answer 4

Julio,

    Sim, é perfeitamente possível, basta você separar cada definição da CTE com vírgula. Veja um exemplo:

;with CTE1 (Campos) as
(select Campos from Tabela1),
CTE2 (Campos)
as (select Campos from Tabela2)
select * from cte1 cross join cte2

---

Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008

Answer 5

Isso aí...

Valeu

Abraço

---

Julio C.