AnsweredAssumed Answered

Running totals grouped by ID - summary vs SQL

Question asked by on Nov 9, 2015
Latest reply on Nov 15, 2015 by

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,