SUM na QTDE e no VLRMED solicitando GROUP BY
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
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
Curtidas 0