Former Member

### Sum of Doctotal in Query Generator

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

Thank you!

10|10000 characters needed characters exceeded

• Jan 13, 2015 at 05:01 AM

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

10|10000 characters needed characters exceeded
• Jan 13, 2015 at 06:24 AM

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