To group data in OutSystems, open the Aggregate's Groups tab, drag the columns you want to group by, then switch to the Columns tab and apply an aggregate function (Count, Sum, Avg, Min, Max) to the numeric columns. Every column that is NOT aggregated must be in the Group By list — TrueChange enforces this automatically.
Summary Queries with the Groups Tab
Group By transforms an Aggregate from a row-level query into a summary query. Instead of seeing one row per employee, you see one row per department with the count of employees in each. This is the equivalent of SQL's GROUP BY clause combined with aggregate functions (COUNT, SUM, AVG, MIN, MAX).
OutSystems implements this in the Aggregate's Groups tab. The Groups tab is separate from Filters and Sorts — this page focuses exclusively on grouping and aggregation logic. It does NOT cover basic Aggregate creation (see outsystems-aggregate-tutorial) or joining entities (see outsystems-aggregate-joins).
Prerequisites
- At least one entity with numeric or quantifiable data (e.g., Employee with Salary, Order with Amount)
- Familiarity with creating Aggregates and the Sources tab (see outsystems-aggregate-tutorial)
- For the cross-entity example: a Department entity linked to Employee via FK
Step-by-step guide
Open the Groups tab and add a Group By column
Open the Groups tab and add a Group By column
Open your Aggregate (or create a new one with the Employee entity as source). Click the Groups tab in the Aggregate editor (fourth tab, after Sources, Filters, Sorts). The Groups tab has two columns: the left side lists all available attributes from your sources; the right side is the 'Group By' area. Drag 'Employee.DepartmentId' from the left column to the Group By area on the right. Because DepartmentId alone isn't human-readable, also drag the Department entity into Sources first (with a With or Without join), then drag 'Department.Name' into Group By instead. As soon as you add any column to Group By, all columns in the Aggregate are evaluated against the rule: every selected column must either be in Group By OR have an aggregate function applied. TrueChange will show errors for any columns that violate this rule.
Expected result: Groups tab shows Department.Name in the Group By area. TrueChange may show errors for other columns — these will be resolved in the next steps.
Apply Count to summarize the number of employees per department
Apply Count to summarize the number of employees per department
Go to the Columns tab (back to the Sources view showing the column list). Find 'Employee.Id' in the column list. Click the dropdown in the Function column next to Employee.Id and select 'Count'. Rename this calculated column to 'EmployeeCount' by double-clicking the column name. The preview pane now shows one row per Department.Name with a count of employees in each department. Count behavior: - Count(Employee.Id): counts rows where Employee.Id is not null — effectively counts all matched employees - Count(*): not directly available in Aggregates; use Count(any non-null column) instead - Count counts only the rows returned by the current join type — if using Only With, employees without a department are excluded from counts
1/* Group By configuration result */2GROUP BY Department.Name3SELECT4 Department.Name AS DepartmentName,5 COUNT(Employee.Id) AS EmployeeCount6FROM Employee7LEFT JOIN Department ON Employee.DepartmentId = Department.Id8GROUP BY Department.Name910/* Accessing in expression: */11GetEmployeesByDept.List.Current.EmployeeCountExpected result: Preview pane shows one row per department with the count of employees. For example: Engineering | 12, Marketing | 7, Sales | 15.
Apply Sum and Avg to numeric columns
Apply Sum and Avg to numeric columns
Add a Salary (Decimal) attribute to Employee entity first if not already present. In the Aggregate column list, find 'Employee.Salary'. Click the Function dropdown → select 'Sum'. Rename it 'TotalSalary'. Add another row for Employee.Salary, Function = 'Avg', name it 'AvgSalary'. Available aggregate functions: - Count: number of non-null rows in the group - Sum: total of all numeric values in the group - Avg: arithmetic mean of numeric values (NULL rows excluded) - Min: smallest value in the group - Max: largest value in the group Sum and Avg require numeric types (Integer, Long Integer, Decimal, Currency). Applying them to Text attributes causes a TrueChange error. For Min and Max, you can apply them to Date and DateTime columns too: Max(Employee.HireDate) returns the most recent hire date per department.
1/* Multiple aggregate functions in one Aggregate */2GROUP BY Department.Name34SELECT5 Department.Name AS DepartmentName,6 COUNT(Employee.Id) AS EmployeeCount,7 SUM(Employee.Salary) AS TotalSalary,8 AVG(Employee.Salary) AS AvgSalary,9 MIN(Employee.Salary) AS MinSalary,10 MAX(Employee.Salary) AS MaxSalary,11 MAX(Employee.HireDate) AS MostRecentHire12FROM Employee13LEFT JOIN Department ON Employee.DepartmentId = Department.Id14GROUP BY Department.Name1516/* Accessing results */17GetDeptSummary.List.Current.TotalSalary18GetDeptSummary.List.Current.AvgSalary1920/* Formatting in expression */21"Avg Salary: $" + DecimalToText(Round(GetDeptSummary.List.Current.AvgSalary, 2))Expected result: Preview pane shows one row per department with EmployeeCount, TotalSalary, AvgSalary, MinSalary, MaxSalary, and MostRecentHire columns all populated.
Sort grouped results by an aggregate value
Sort grouped results by an aggregate value
Click the Sorts tab. The sort column list now includes your aggregate columns (EmployeeCount, TotalSalary, AvgSalary) in addition to the Group By columns. Drag 'EmployeeCount' to the Sorts area. Set direction to Descending. This sorts departments from largest to smallest employee count. Important: when using Group By, you can only sort by: 1. Columns that are in the Group By list (e.g., Department.Name) 2. Calculated aggregate columns (e.g., EmployeeCount, TotalSalary) Attempting to sort by a non-grouped, non-aggregated column (e.g., Employee.LastName) causes a TrueChange error — TrueChange will say 'Column is not valid in the GROUP BY clause.'
Expected result: Preview pane shows departments sorted from most employees to fewest. The Sorts tab shows EmployeeCount Descending in the sort list.
Group by multiple columns for finer granularity
Group by multiple columns for finer granularity
For more detailed groupings, add multiple columns to the Group By area. For example, to see employee counts by Department AND Employment Status: 1. Add EmploymentStatus to Sources (with Only With join via Employee.StatusId) 2. In Groups tab, drag both Department.Name and EmploymentStatus.Label to Group By 3. In the Columns tab, apply Count to Employee.Id 4. Sort by Department.Name Ascending, then EmployeeCount Descending Result: one row per unique combination of Department + Status. For example: - Engineering | Full Time | 10 - Engineering | Part Time | 2 - Marketing | Full Time | 5 The number of result rows = the number of distinct combinations across all Group By columns.
1/* Multi-column Group By */2GROUP BY Department.Name, EmploymentStatus.Label34SELECT5 Department.Name AS DepartmentName,6 EmploymentStatus.Label AS StatusLabel,7 COUNT(Employee.Id) AS EmployeeCount8FROM Employee9INNER JOIN Department ON Employee.DepartmentId = Department.Id10INNER JOIN EmploymentStatus ON Employee.StatusId = EmploymentStatus.Id11GROUP BY Department.Name, EmploymentStatus.Label12ORDER BY Department.Name ASC, COUNT(Employee.Id) DESC1314/* Display in a Table widget: */15GetDeptStatusCount.List.Current.DepartmentName16GetDeptStatusCount.List.Current.StatusLabel17GetDeptStatusCount.List.Current.EmployeeCountExpected result: Preview shows multiple rows per department (one per status type). Each row has DepartmentName, StatusLabel, and EmployeeCount columns.
Display grouped results in a Table widget on a screen
Display grouped results in a Table widget on a screen
Close the Aggregate editor and return to the Screen editor. Drag a Table widget from the Toolbox onto the screen canvas. Set the Table's Source property to `GetDeptSummary.List`. OutSystems auto-generates table columns based on the Aggregate's output columns. For each column: - Header: set to descriptive text ('Department', 'Headcount', 'Avg Salary') - Expression: `GetDeptSummary.List.Current.DepartmentName`, `GetDeptSummary.List.Current.EmployeeCount`, `Round(GetDeptSummary.List.Current.AvgSalary, 2)` For numeric formatting, wrap in DecimalToText or use a label expression: `"$" + DecimalToText(Round(GetDeptSummary.List.Current.AvgSalary, 0))` For an 'All Departments Total' row at the bottom, add a second Aggregate with no Group By and only Count and Sum functions applied.
Expected result: A Table widget on the screen displays one row per department with headcount and salary summary columns. The table updates when data changes after publishing and refreshing.
Complete working example
1/* =====================================================2 Aggregate: GetDepartmentSummary3 Purpose: Employee count + salary stats per department4 ===================================================== */56/* Sources tab */7Entity 1: Employee (base)8Join: With or Without /* LEFT JOIN — include depts with 0 employees later */9Entity 2: Department10Join: Only With /* INNER JOIN — all employees have a status */11Entity 3: EmploymentStatus1213/* Groups tab — Group By columns */14Department.Name15EmploymentStatus.Label1617/* Columns tab — Aggregate functions applied */18Column Function Alias19Employee.Id Count EmployeeCount20Employee.Salary Sum TotalSalary21Employee.Salary Avg AvgSalary22Employee.Salary Min MinSalary23Employee.Salary Max MaxSalary24Employee.HireDate Max MostRecentHire2526/* Sorts tab */27Department.Name Ascending28EmployeeCount Descending2930/* Max Records: 200 */3132/* Aggregate output type (auto-generated record definition) */33{34 DepartmentName: Text35 StatusLabel: Text36 EmployeeCount: Long Integer37 TotalSalary: Decimal38 AvgSalary: Decimal39 MinSalary: Decimal40 MaxSalary: Decimal41 MostRecentHire: Date42}4344/* Accessing in screen expressions */45GetDepartmentSummary.List.Current.DepartmentName46GetDepartmentSummary.List.Current.EmployeeCount47Round(GetDepartmentSummary.List.Current.AvgSalary, 2)4849/* Grand total count (from separate Aggregate with no Group By) */50GetAllEmployees.CountCommon mistakes
Why it's a problem: Adding an employee-level column (e.g., Employee.Email) to a grouped Aggregate without applying a function
How to avoid: Every column not in the Group By list must have an aggregate function (Count, Sum, Avg, Min, Max). For text columns that don't make sense to aggregate, either remove them from the Aggregate output or add them to the Group By list. TrueChange will show a red error and block publishing until this is resolved.
Why it's a problem: Sorting by a non-grouped, non-aggregated column in a grouped Aggregate
How to avoid: In a GROUP BY query, you can only sort by Group By columns or aggregate result columns. In the Sorts tab, only drag columns that are either in your Group By list or are named aggregate results (EmployeeCount, TotalSalary, etc.). Attempting to sort by Employee.FirstName in a department-grouped Aggregate will cause a TrueChange error.
Why it's a problem: Using Count(Employee.Id) and getting 0 for departments with no employees when using a LEFT JOIN
How to avoid: When using LEFT JOIN (With or Without), departments with no employees will have Employee.Id = NULL for all rows. COUNT(NULL) = 0, which is correct behavior. However, if you used Only With (INNER JOIN), departments with no employees won't appear at all. If you need departments with zero employees, reverse the join direction: make Department the first (left) entity and Employee the second, then use With or Without.
Why it's a problem: Expecting the Aggregate to update automatically after filtering data on screen
How to avoid: Grouped Aggregates, like all Aggregates, do not auto-refresh when filters change. If you add a department filter input to the screen, wire it to a filter condition in the Aggregate's Filters tab, then add a Refresh Data node in the filter input's OnChange Client Action to re-execute the Aggregate.
Best practices
- Always check TrueChange after adding Group By columns — every non-aggregated column must be in Group By. TrueChange enforces this rule and blocks publishing until all columns comply.
- Name aggregate columns descriptively (EmployeeCount, not Count_1) — the column names become the output record structure field names that you reference throughout your action flows and screen expressions.
- Use Max Records with grouped Aggregates — even though grouping reduces the number of rows, a large dataset can still produce many groups. Set a reasonable limit.
- For 'Top N by metric' queries, combine Group By with Sort Descending on the aggregate column and Max Records = N.
- Avoid mixing row-level and group-level data in a single Aggregate — if you need both employee details and department totals, use two separate Aggregates.
- When using Avg, be aware that NULL values are excluded from the calculation. OutSystems uses the database's native AVG behavior. If employees with no salary (null/0) should be counted, use Sum/Count and calculate the average in an expression instead.
Still stuck?
Copy one of these prompts to get a personalized, step-by-step explanation.
I'm building an OutSystems O11 Reactive Web app with an Employee entity (fields: Id, FirstName, LastName, Salary, DepartmentId, HireDate) and a Department entity (Id, Name). Write an Aggregate configuration that groups employees by department and shows: department name, employee count, total salary, average salary, and most recent hire date. Include the Groups tab setup and Sorts tab configuration.
In my OutSystems Aggregate with Employee and Department entities, I need to show the top 10 departments by headcount with average salary. Describe the Groups tab configuration, aggregate function settings, sort order, and Max Records setting. Also show how to display this in a Table widget on a Reactive Web screen.
Frequently asked questions
How do I count rows in an OutSystems Aggregate without using Group By?
Without Group By, use GetEmployees.Count — this returns the total number of rows matching your Aggregate's filters. You do not need any aggregate function for a simple row count. The .Count property is always available on any Aggregate. If you want COUNT(*) as a column within a query, add Employee.Id to the Aggregate output with the Count function applied and no Group By columns — this returns a single row with the total count.
Can I filter by an aggregate result, like 'show only departments with more than 5 employees'?
Not directly in the Filters tab — OutSystems Aggregate filters correspond to SQL WHERE clauses, which run before grouping. To filter by an aggregate result (equivalent to SQL HAVING), you must either: (1) use an Advanced SQL query with a HAVING clause, or (2) apply the filter in your action flow after fetching the grouped results by iterating through the list and skipping records where EmployeeCount <= 5.
What data type does the Count aggregate function return in OutSystems?
Count returns a Long Integer in OutSystems. When displaying it in an expression, you may need to convert it: IntegerToText(GetDeptSummary.List.Current.EmployeeCount). Sum returns the same type as the source column (Decimal for Decimal columns, Long Integer for Integer columns). Avg always returns Decimal.
Can I group by a calculated attribute I added in the Aggregate?
Yes. Add your calculated attribute first (e.g., Year(Employee.HireDate) named HireYear), then in the Groups tab, drag HireYear to the Group By area. This lets you group by derived values like hire year, month, or day of week. Calculated attributes appear in the Groups tab's available column list alongside entity attributes.
Talk to an Expert
Our team has built 600+ apps. Get personalized help with your project.
Book a free consultation