0 Replies Latest reply on Apr 19, 2015 11:19 PM by jlcalhoun

    grouping and summarizing data

    jlcalhoun

      Title

      grouping and summarizing data

      Post

      I process over 1000 records that are transactions for many customers per day.  The table has a customer ID field, a product ID and a cost for that product.  Each record is for a single unit.  I need to populate a temporary table daily for each batch that takes each Customer ID and calculates the total number for each Product ID and calculates the cost for that Product ID for that Customer ID.

      Example:

      Input table

      Customer ID                       Product ID                           Product ID count                              cost

      A001                                      123                                         1                                                              $5

      A001                                      123                                         1                                                              $5

      B001                                      123                                         1                                                              $5

      B001                                      123                                         1                                                              $5

      B001                                      123                                         1                                                              $5

      B001                                      123                                         1                                                              $5

      B001                                      111                                         1                                                              $5

       

      NEEDED SUMMARY TABLE

      Customer ID                       Product ID                           Product ID count              cost

      A001                                      123                                         2                                              $10

      B001                                      123                                         4                                              $40

      B001                                      111                                         1                                              $5