Advanced Operations - Aggregation Operation

In the previous section,you have created a view that obtained the billing information for all the customers in your database. In this view we have one record for each bill, but we want to calculate the total amount due by the customers instead of having the separate bills.

To do so, you can create a new view that aggregates (group by) the different customers using the customer_id to compute the total amount.

To create the new aggregation view you can follow these steps:

  1. Right click on the client_with_bills view and select New > Selection.
  2. In the Projection / Selection View wizard go to the Group by tab and check the Use group by checkbox.
  3. Click on Ok in the pop-up dialog.
  4. From the list of fields on the left Add the following to the Selected group by fields and expressions box: name, surname and client_id.
    Join wizard model tab
  5. Go to the Output tab and rename the view to amount_due_by_client.
  6. Now, you have to create a new field that computes the addition of the amount due for each client. Click on New aggr. field button and define a new field called total_amount that will be calculated using the SUM aggregation function and the amount_due field.

    SUM(CAST('float', client_with_bills.amount_due))

    CODE
    New aggregation field
  7. Click Ok to add the new field to the view.
  8. And click to confirm the creation of the view.

Since the amount_due field comes as a text from the Web service, you will have to cast the field to a numeric value using the CAST function.

NOTE

Now, if you execute the new view, you will get as result one record per customer with the total billing balance:

amount_due_by_client execution results