Aggregation (subtotals) operations compute a single value from a collection of values using operators such as Sum, Min, Max, Count, etc. The most basic difference between Aggregation operations and Simple operations is that aggregation uses grouping. Aggregation without grouping is also possible but in that case it produces a single row of grand totals. Grouping is needed to produce subtotals. Input rows are grouped by values of one or more columns, and subtotals are calculated for each of those groups.

The following syntax illustrates an aggregation query that calculates subtotals for each products, so the number of resulting row is equal to the number of products. For each product, two subtotals (aggregations) are calculated. These subtotals are number of orders including that product, and maximum discount for that product in those orders.

Syntax

Dim query2 As dynamic = workspace.query("subtotals", New With { _
        Key .Product = od.ProductID, _
        Key .OrdersCount = Op.Count(od.OrderID), _
        Key .MaxDiscount = Op.Max(od.Discount) _
})

dynamic query2 = workspace.query("subtotals", new
    {
        Product = od.ProductID,
        OrdersCount = Op.Count(od.OrderID),
        MaxDiscount = Op.Max(od.Discount)
    });

While using aggregation, you can group more than one column. The following query illustrates this as it has two grouping columns as a result of which there will be more groups in the result.

Syntax

Dim query3 As dynamic = workspace.query("subtotals3", New With { _
        Key .Product = od.ProductID, _
        Key .Discount = od.Discount, _
        Key .OrdersCount = Op.Count(od.OrderID) _
})

dynamic query3 = workspace.query("subtotals3", new
    {
        Product = od.ProductID,
        Discount = od.Discount,
        OrdersCount = Op.Count(od.OrderID)
    });

In the above query results in more than one group each containing orders including a certain product with a certain discount, and the subtotal will show the number of orders in the group.