Help with query sales and credit memo

[former_member540313]

I have a query that give me total line and credit memo......i made a subquery to sum fields but the result is not that i wish.

the query show:

Family total

family1 500

family1 -300

family2 600

family2 -100

I want only:

family1 200

family2 500

Select Name, sum(isnull(Total,0)) as Total, sum(isnull(GBruta,0)) as GBruta, ProdA FROM (
SELECT T3.[ItmsGrpNam] as Name, sum(IsNULL(T1.[TotalSumSy],0)) as Total, T3.U_gpo1 as ProdA,sum(IsNULL(T1.GrssProfSC,0)) as 'GBruta'

FROM OINV T0
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]
INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
WHERE T0.[DocDate] >='20170101' AND T0.[DocDate] <='20170731'

GROUP BY T3.[ItmsGrpNam], T3.U_gpo1

UNION ALL

SELECT T3.[ItmsGrpNam] as Name, sum(IsNULL(T1.[TotalSumSy],0))*-1 as Total, T3.U_gpo1 as ProdA,sum(IsNULL(T1.GrssProfSC,0))*-1 as 'GBruta'

FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]
INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
WHERE T0.[DocDate] >='20170101' AND T0.[DocDate] <='20170731' AND T1.BaseType<>'203'
GROUP BY T3.[ItmsGrpNam], T3.U_gpo1)xx1
GROUP BY Name, ProdA, GBruta

can anyone help with this please

[former_member540313 · Accepted solution]

My problem was:

GROUP BY Name, ProdA, GBruta <--

I did

GROUP BY Name, ProdA

And now works, thnks