Grouped Customers Related to Grouped Orders with Aggregation

Important

This feature will be removed in a future version of Microsoft Data Access Component. Avoid using this feature in new development work and plan to modify applications that currently use this feature. Applications that use MSDADS should migrate to XML.

By nesting SHAPE commands, you can use the Data Shaping Service for OLE DB to create hierarchies with as many levels as you want and to aggregate child rows into parent rows. The grouped aggregation example uses three SHAPE commands to create a four-level hierarchy and uses the SUM function to aggregate child rows.

In the following code example, data is retrieved from the Customers and Orders tables in the Sales database. The innermost SHAPE command uses select * from orders to create the lowest rowset in the hierarchy. The clause COMPUTE rsOrdDetail BY cust_id, month defines its parent, and the SUM function aggregates the child rowsets into totalAmtPerMonth.

The middle SHAPE command uses select * from Customers to create a parent rowset whose child is the parent rowset in the inner SHAPE command. The phrase SUM(rsOrdByMonth.totalAmtPerMonth) AS totalAmtPerCustomer aggregates the previous total, totalAmtPerMonth, and sums it by rsOrdbyMonth for all orders selected into totalAmtPerCustomer.

The outermost SHAPE command creates the root rowset in the hierarchy from the parent rowset in the middle SHAPE command. It is created by partitioning the Customers rowset by state, and totalAmtPerState then holds the total figure of the first two aggregations.

SHAPE
   (SHAPE {select * from Customers}
         APPEND SUM(rsOrdByMonth.totalAmtPerMonth)
            AS totalAmtPerCustomer,
            ((SHAPE {select * from Orders}
                  AS rsOrdDetail
                        COMPUTE SUM(rsOrdDetail.order_amt)
                           AS totalAmtPerMonth, rsOrdDetail
                              BY cust_id, month)
         RELATE cust_id TO cust_id)
            AS rsOrdByMonth )
      AS rsCustDetail
   COMPUTE SUM(rsCustDetail.totalAmtPerCust)
      AS totalAmtPerState, rsCustDetail
   BY state