AnsweredAssumed Answered

Running totals grouped by ID - summary vs SQL

Question asked by danielkellett@mac.com on Nov 9, 2015
Latest reply on Nov 15, 2015 by 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

Outcomes