SUM solicitando GROUP BY

14/12/2021

9

Preciso adicionar o sum em QTDE_SAI e QTDE_DEV, VLRMED_SAI e VLRMED_DEV mas quando rodo a query exige um GROUP BY, alguém me ajuda.

SELECT
/*SAIDA*/
GELANSAI.ITEM ITEM_SAIDA,
GECADSAI.DATA DATA,
SUM(GELANSAI.QTDE) QTDE_SAIDA,
SUM(GELANSAI.VLRMED) VLRMED_SAIDA,
GECADSAI.CDC CDC_COD,
/*ITENS*/
I.NOME,
I.UNI_CON,
I.CUSTO VLR_UNI,
(SELECT FIRST 1 GEGRUPOS.NOME FROM GEGRUPOS WHERE GEGRUPOS.COD = I.GRU) GRUPO,
(SELECT FIRST 1 NOME FROM TBCENCUS WHERE COD=GECADSAI.CDC) CDC,
/*DEVOLUCAO*/
GELANDEV.ITEM ITEM_DEV,
GECADDEV.DATA DATA,
SUM(GELANDEV.QTDE) QTDE_DEV,
SUM(GELANDEV.VLRMED VL)RMED_DEV,
GECADDEV.CDC CDC_COD

FROM GECADSAI
LEFT JOIN GELANSAI ON GECADSAI.ANO=GELANSAI.ANO
AND GECADSAI.MES=GELANSAI.MES
AND GECADSAI.DOC=GELANSAI.DOC
LEFT JOIN GEITENS I ON GELANSAI.ITEM=I.COD
LEFT JOIN TBCENCUS T ON T.COD=GECADSAI.CDC
LEFT JOIN GELANDEV ON GELANDEV.ID_GELANSAI=GELANSAI.ID_GELANSAI
LEFT JOIN GECADDEV ON GECADDEV.ID=GELANDEV.ID_GECADDEV
WHERE GECADSAI.ANO=:ANO
AND GECADSAI.MES=:MES
AND (
(NOT GECADSAI.CDC IS NULL AND GECADSAI.CDC BETWEEN :CDC1 AND :CDC2)
OR
(NOT GECADDEV.CDC IS NULL AND GECADDEV.CDC BETWEEN :CDC1 AND :CDC2)
)
AND (
(NOT GECADSAI.DATA IS NULL AND GECADSAI.DATA BETWEEN :DATA1 AND :DATA2)
OR
(NOT GECADDEV.DATA IS NULL AND GECADDEV.DATA BETWEEN :DATA1 AND :DATA2)
)
AND (COALESCE(GELANSAI.CONSOL,'''')=''T'' OR COALESCE(GECADSAI.CONSOL,'''')=''T'')
ORDER BY CDC, NOME
Marcos Batista

Marcos Batista

Responder

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários,
consulte nossa política de privacidade.

Aceitar