6 Replies Latest reply on Nov 15, 2015 2:36 PM by danielkellett@mac.com

    Running totals grouped by ID - summary vs SQL

    danielkellett@mac.com

      Hi guys - thanks for your helpful tips on my last question, which is now all sorted (no pun intended)

       

      Here's the jam

       

      I was given a table with a list of customers, products, and the day they were purchased.

      I hacked a "unique ID" - a text concatenation of the 3, so there would be an identifier to count the number of times each customer has purchased each product in a given year. The day is included because of duplicates in other fields (e.g. dispatch_date)

       

      CUSTOMER_ID     PRODUCT_ID     ORDER_DATE                 [my "unique ID"]

      1                              2                                   01-JAN                     1|2|1   [final digit is day of year, to keep it relatively simple!]

      1                              2                                   12-JAN                      1|2|12 etc...

      1                              1                                   16-JAN

      2                              2                                   11-JAN                                  

                                  2                                   16-JAN

                                  2                                   19-JAN

       

       

      I have tried a using a field with a summary function, i.e. count of "unique ID", sorted by "unique ID" - i.e. I'd expect to get 2 orders for the first 2 rows, then 1, then 3. Instead I only get a running total (i.e. row number of the table).

       

      What I really want to do is ExecuteSQL("SELECT DISTINCT COUNT(unique_ID) FROM MyTable GROUP BY unique_ID" ;"";"";"")

       

      ...have also tried adding WHERE unique_ID = ?, and the parameter Unique_ID to the ExecuteSQL statement.

       

      All I get is a question mark.

      I've added a row to get(lastODBC error) [pseudo code] but this is also empty.

       

      Any ideas?

       

      Thanks in advance,

      Dan

        • 1. Re: Running totals grouped by ID - summary vs SQL
          okramis

          What I really want to do is ExecuteSQL("SELECT DISTINCT COUNT(unique_ID) FROM MyTable GROUP BY unique_ID" ;"";"";"")

          You need to place the DISTINCT inside the COUNT clause like:

           

          ExecuteSQL ( "SELECT CUSTOMER_ID, PRODUCT_ID, COUNT(DISTINCT(unique_ID)) FROM MyTable GROUP BY CUSTOMER_ID, PRODUCT_ID" ; char(9) ; "" )

           

          returns:

          111
          122
          223

           

          Regards

          Otmar

          • 2. Re: Running totals grouped by ID - summary vs SQL
            danielkellett@mac.com

            Hi - thanks a lot.

            I'm still a bit fazed by Filemaker SQL

            According to your method, I get a field with multiple rows.

             

            Obviously what I sent you was dummy data - what I'm seeing is:

            Untitled 1.tiff

            with the last row being the SQL result, which "unfolds" into multiple rows - I'm really not used to that, having worked with Oracle & Teradata before....

             

            When I open one of these rows-within-rows, I see:

             

            Untitled 2.tiff

             

            which doesn't quite make sense. I get lots of sixes, but there should be two occurrences of 20001|30003|312

             

            Is the char(9) appropriate for the data I'm actually using (rather than the dummy data)?

             

            Should I even be putting this field with ExecuteSQL calculation in the main table, or in a "view"/copy of relevant parts of the table?

             

            Anyway, thanks for your help, at least I'm starting to get SQL to work in Filemaker. Not just question marks!

             

            Dan

            • 3. Re: Running totals grouped by ID - summary vs SQL
              okramis

              I'm still not clear about what you try to achieve. But if you want a field per row whitch counts products per customer, you would do something like this:

               

              ExecuteSQL ( "SELECT COUNT(DISTINCT(unique_ID)) FROM MyTable WHERE CUSTOMER_ID=? AND PRODUCT_ID=?" ; "" ; "" ; MyTable::CUSTOMER_ID ; MyTable::PRODUCT_ID  )


              maybe there's no need for the distinct, as it might be possible, that a customer orders the same product more than once per day.

              • 4. Re: Running totals grouped by ID - summary vs SQL
                danielkellett@mac.com

                Hi again.

                 

                Let me to clear:

                 

                I'm trying to create a unique ID, which as usual the previous developer (working in excel!) did not include and didn't think necessary, but it really is.

                 

                I have a table with (amongst other columns), PK, date, customerID, productID, eventtype

                 

                The event type could be (ordered, shipped, invoice sent, invoice received). So there are multiple rows for each time a customer orders a particular product.

                 

                So I need a running total of the number of times a customer has ORDERED a certain product, AND the running total must be strictly chronological, i.e. 1 is the first time, 2 is the second time. If I just do a summary, I will get a running total including all the rows of invoicing and payment data

                 

                If I execute the SQL you suggest above, which works fine, I just get the total number of orders per customer and product in each row.

                 

                I basically want to make the field a summary field (where you would use the dialog to check Running Total, and then select the field by which to sort. But Filemaker only allows you to select one field. If the dialog allowed me to select 2, all would be well).

                 

                So I want to do something like

                 

                ExecuteSQL (

                "

                SELECT count(EventID_pk)

                FROM events

                WHERE CustomerID_fk=?

                AND Product_fk =?

                AND EventTypeID_fk =?

                "

                ; "" ; "" ; events::CustomerID_fk ; events::ProductID_fk

                ; events::EventTypeID_fk=1 ) - This last argument filters for Event Type 1, which is the ORDER of the product. Is this the right place to put this?

                 

                In traditional SQL I would have used a "partition by (Customer_ID order by Product_ID)" I think that's the syntax - but ExecuteSQL doesn't seem to recognise that

                • 5. Re: Running totals grouped by ID - summary vs SQL
                  okramis

                  I have a table with (amongst other columns), PK, date, customerID, productID, eventtype

                   

                  The event type could be (ordered, shipped, invoice sent, invoice received). So there are multiple rows for each time a customer orders a particular product.

                   

                  So I need a running total of the number of times a customer has ORDERED a certain product, AND the running total must be strictly chronological, i.e. 1 is the first time, 2 is the second time. If I just do a summary, I will get a running total including all the rows of invoicing and payment data

                   

                  If I execute the SQL you suggest above, which works fine, I just get the total number of orders per customer and product in each row.

                   

                  So I want to do something like

                   

                  ExecuteSQL (

                  "

                  SELECT count(EventID_pk)

                  FROM events

                  WHERE CustomerID_fk=?

                  AND Product_fk =?

                  AND EventTypeID_fk =?

                  "

                  ; "" ; "" ; events::CustomerID_fk ; events::ProductID_fk

                  ; events::EventTypeID_fk=1 ) - This last argument filters for Event Type 1, which is the ORDER of the product. Is this the right place to put this?

                   

                  do it just like this:

                   

                  ExecuteSQL ( "

                   

                  SELECT count(EventID_pk)     //could also just be "count(*)"

                  FROM events

                  WHERE CustomerID_fk=?

                  AND Product_fk =?

                  AND EventTypeID_fk =?

                   

                  " ; "" ; "" ; events::CustomerID_fk ; events::ProductID_fk; 1

                  )

                   

                  if you need the running count add this argument:

                   

                  ..........

                  AND \"date\"<=?

                   

                  " ; "" ; "" ; events::CustomerID_fk ; events::ProductID_fk; 1; events::date )

                  • 6. Re: Running totals grouped by ID - summary vs SQL
                    danielkellett@mac.com

                    DUH! That's so obvious, how did I miss that?! I will hit myself on the head with a SQL manual!

                     

                    Thanks a lot. Showing me the syntax in the executesql function - which is a bit weird. Not as weird as LINQ....

                     

                    Thanks again!

                    D