1 Reply Latest reply on Feb 23, 2015 2:27 PM by philmodjunk

    Keep Count of Distinct Values



      Keep Count of Distinct Values


      Hi, I did not find my answer in the forum and was hoping that some one can help me. I have a table called Orders with a field called CustID. I would like to keep a count of how many times the CustID repeats in each record so that a table with the following values:

      Record1  CustID=1

      Record2 CustID=2

      Record3 CustID=1

      Will have a field that returns the following values

      Record1 NumOfOrders=2

      Record2 NumOfOrders=1

      Record3 NumOfOrders=2

      I can get a static answer if I write the following calculation in NumOfOrders:

      ExecuteSQL ("SELECT Distinct COUNT (CustID) FROM Orders

      WHERE CustID = '1'" ; "" ; ""  )

      But I need it to be dynamic and look at the value of the CustID field of the record that it is on but I can not get it to work:

      ExecuteSQL ("SELECT Distinct COUNT (CustID) FROM Orders

      WHERE CustID = CustID" ; "" ; ""  )

      What am I doing wrong? Please Help

        • 1. Re: Keep Count of Distinct Values

          But from the context of what table?

          If you have this relationship:

          Customers::CustID = Orders::CustID

          Then Count ( Orders::CustID ) can be defined in Customers and you can then see the number of orders for each customer from layouts based on the customers table.

          This could also be done with ExecuteSQL, but the above process seems to me to be much simpler and easier to set up.