4.2 Aggregates: Joins, Group By, and Computed Attributes

Quick Answer: OutSystems Aggregates go beyond single-table reads: you can join multiple entities with With or Only With joins, collapse rows with Group By to compute counts and sums, and add computed attributes — formula-driven columns calculated per row inside the query. When a query exceeds what Aggregates can express (subqueries, UNION, arbitrary SQL functions, or external data), you switch to the SQL element. Knowing the boundary is a heavily tested skill.

Joins: With and Only With

When an Aggregate has more than one source entity, OutSystems generates a join. You add a second entity by dragging it onto the canvas and choosing the relationship. There are two join types available in Aggregates:

The direction matters. With is symmetric in result; Only With is not — Only With keeps every row from the entity already on the canvas (the first entity), even when there is no match on the second entity you are adding. A common exam formulation: I want every Order even if it has no OrderItem. You would put Order on the canvas first, then add OrderItem with Only With, and the result preserves Orders that have no items. Reversing this gives OrderItem rows without an Order, which is rarely what you want. Joins are defined by a join condition, typically EntityA.Id = EntityB.FKAttribute. The condition is type-checked: identifiers must match. You can add additional filter conditions on top of joins to narrow the result set further.

Group By

Group By collapses rows sharing the same values in one or more grouped attributes and computes an aggregate function over each group. After selecting an attribute to group by, OutSystems exposes the grouping in the Aggregate's output. The supported aggregate functions are:

A typical use: list each Customer with the count and total of their Order records. You group by Customer.Id (and any customer display attributes you want in the output), then add Count on Order.Id and Sum on Order.TotalAmount. The output List now has one record per customer with the aggregated columns alongside the customer attributes. A critical constraint: every non-aggregated attribute in the output must be included in the Group By. This mirrors SQL's GROUP BY rule. If you include Customer.Name in the output but forget to group by it, the platform either errors or auto-adds it to the grouping, depending on context — but the exam expects you to know the rule.

Computed Attributes (Calculated Columns)

A computed attribute is a column whose value is calculated per row by a formula, evaluated inside the Aggregate. You add one by right-clicking the output and choosing Add Computed Attribute, then writing an expression such as Order.TotalAmount * 1.20 or If(Order.StatusId = 1, "Pending", "Done"). Computed attributes are part of the generated SQL when possible, so they execute server-side; otherwise the platform evaluates them locally on the result set after it returns. Computed attributes are useful for derived display values (formatted dates, concatenated strings, conditional labels), math on numeric columns (totals, discounts, percentages), and simplifying bindings in the UI by pre-computing values so the client avoids repetitive expressions. They differ from attributes chosen directly from an entity: a computed attribute's value is recalculated for every row at query time, while a direct attribute is just read from the table. This has performance implications — heavy computed attributes on large result sets add CPU cost at query time.

When to Use Aggregates vs SQL

Aggregates cover most read needs, but they cannot express every query. The boundary is a frequent exam topic.

When you hit one of the SQL-only cases, use the SQL element inside a Server Action, write the query with @-prefixed parameters, and map results to a Structure. The SQL element is covered in detail in section 4.4. For simple reads, always prefer an Aggregate — it is optimized, type-safe, schema-aware, and easier to maintain than hand-written SQL.