cancel
Showing results for 
Search instead for 
Did you mean: 

Sum of Doctotal in Query Generator

Former Member
0 Kudos

Hi All,

I am currently trying to create a query that has the total of all documents per customer but am having trouble trying to figure out how to calculate. Currently I have:

SELECT T0.[CardCode], T0.[CardName], T1.[City], T1.[State], T2.[DocNum], T2.[DocDueDate], T2.[DocTotal] FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode INNER JOIN ORDR T2 ON T0.CardCode = T2.CardCode

Please help!

Thank you!

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

Try

SELECT T2.[City], T2.[State], T0.[DocNum], T0.[DocDueDate],sum(T0.[DocTotal]), T0.[CardCode], T0.[CardName] FROM ORDR T0  INNER JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode] left JOIN CRD1 T2 ON T1.[CardCode] = T2.[CardCode] WHERE T0.[DocStatus] =[%0] GROUP BY T0.[DocNum], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T2.[State], T2.[City]

Regards

Bhanu

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try:

SELECT T0.[CardCode], T0.[CardName], T1.[City], T1.[State], T2.[DocNum], T2.[DocDueDate], sum(T2.[DocTotal]) FROM OCRD T0  left JOIN CRD1 T1 ON T0.CardCode = T1.CardCode inner JOIN ORDR T2 ON T0.CardCode = T2.CardCode where T0.[ShipToDef] = T1.[Address] and T2.docstatus = '[%0]'

group by T0.[CardCode], T0.[CardName], T1.[City], T1.[State], T2.[DocNum],T2.[DocDueDate]

Thanks & Regards,

Nagarajan