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:
- Click on the client_with_bills view and select
New > Selection. - In the Projection / Selection View wizard go to the Group by tab and enable the Use group by Checkbox.
- Click on Ok in the pop-up dialog.
- 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.
- Go to the Output tab and rename the view to amount_due_by_client.
- Now, you have to create a new field that computes the addition of the amount due for each client. Click on New > 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
- Click Ok to add the new field to the view.
- And click
to confirm the creation of the view.
Since the amount_due field comes as a float from the Web service, you will have to cast the field to a numeric value using the CAST function.
Now, if you execute the new view, you will get as result one record per customer with the total billing balance:
