2 Replies Latest reply: Feb 22, 2017 8:45 AM by Giulio Andrea Toscano RSS

    Nested aggregation in a table

    Giulio Andrea Toscano

      Hi,

       

      I am facing a big problem: I have to aggregate in my application on basis of a count statesmen. Follow one example:

       

        MY DATA:

      CustomerProductDayID
      GiulioCaffe101
      GiulioPizza202
      GiulioCaffe303
      GiulioCaffe404
      TomCaffe105
      TomPizza206
      TomPizza307
      TomLatte408
      StefanLatte109
      StefanLatte410

       

      I want to built the following table :

          

      TABLE A:

      Product# Customer once# Customer twice# Customer Three times
      Pizza110
      Caffe101
      Latte110

       

      In order to built the table above (my last deliverable) I have to pass for the following table:

       

      Giulio Caffe 3

                Pizza 1

      Tom   Caffe 1

                Pizza 2

                Latte 1

      Stefan Latte 2

       

      I try to summarize: I am able to create the above table with the follow expression aggr(count((distinct ID), Product, Customer) but in order to create the Table A I need a nested aggregation (?) because I need to count the number of customers that have try one service once, twice or three times.

       

       

      Any help would be appreciated.