Aggregation transformation is used to compute a single value from a collection of values. A few examples for this type of transformation includes average, minimum, sum etc. The basic difference that sets apart an Aggregation operation from a Simple operation is the use of grouping. However, aggregation without grouping is also possible, but in that case it will produce a single row of grand totals.

Grouping is a prerequisite for generating subtotals. In aggregation, the input rows are grouped by values of one or more columns, and the subtotals are calculated for each such groups.

The DataEngine supports Aggregation operations such as Avg, Count, First, Last, Max, Min, Std, StdP, Sum, Var, and VarP.

Defining a query to perform aggregation operations

This section describes how a user can formulate a DataEngine query to perform aggregation operations on base tables. The afore-mentioned aggregation operations are methods in the Op class of the C1.DataEngine assembly, which are invoked while defining the query used to perform an aggregation operation.

Consider an example where a user has imported the data from the “Invoices” database table to the DataEngine base table named “Invoices”. (Refer Connect DataEngine to Database topic for more details.) Using this data, if the user wishes to find out the total sales value by country, then it can be done by defining the following query using the Sum method of the Op class, which would perform the Sum aggregation operation:

// Retrieve the base table for use in constructing queries
dynamic invoice = workspace.table("Invoices");

// Find the total sales by country
dynamic query = workspace.query("SalesByCountry", new
{
    invoice.Country,
    Sales = Op.Sum(invoice.ExtendedPrice)
});

Upon execution, this query groups the ‘Country’ column present in the ‘Invoices’ base table and then calculates the summation of sales for each country. Hence, the resulting ‘SalesByCountry’ table contains two columns ‘Country’ and ‘Sales’ with number of rows equal to the number of countries in the base ‘Invoices’ table.

While using aggregation, you can also group more than one column.

The following query performs grouping on two columns ‘Country’ and ‘ProductName’ and shows the summation of sales of each product in each country.

// Retrieve the base table for use in constructing queries
dynamic invoice = workspace.table("Invoices");
// Find the total sales of each product in each country
dynamic query = workspace.query("ProductSalesByCountry ", new
{
    invoice.ProductName,
    invoice.Country,
    Sales = Op.Sum(invoice.ExtendedPrice)
});