Skip to Content
avatar image
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

Please help!

Thank you!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • 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

    Add comment
    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

    Add comment
    10|10000 characters needed characters exceeded