Idea - Allow Aggregate to output non-grouped attributes when functionally dependent on Group By

Allow Aggregate to output non-grouped attributes when functionally dependent on Group By

New

Aggregates & Queries

Problem

When using Aggregates, OutSystems requires that all attributes in the Output must either be included in the Group By clause or wrapped in an aggregate function (Sum, Count, Max, Min, etc.).

In many scenarios, developers need to group by a single attribute but also display other attributes that are functionally dependent on that group. Currently, this forces developers to either:

Add unnecessary fields to the Group By (which can negatively impact performance and change result granularity), or
Use artificial aggregations like Max() or Min() just to expose the attribute in the output.

This creates unnecessary complexity and may lead to confusion or performance issues.

Impact

More complex and less readable queries
Potential performance degradation due to excessive grouping
Workarounds that do not represent the developer’s actual intent
Additional learning curve for new developers

Expected BehaviorAllow Aggregates to output attributes that are functionally dependent on grouped attributes without requiring them to be included in Group By or wrapped in artificial aggregation functions.

Possible approaches could include:

Automatic detection of functional dependencies (e.g., attributes from 1-to-1 relationships)
A configuration option to mark attributes as dependent on grouped fields
More flexible output configuration in Aggregates

Use Case ExampleGrouping orders by CustomerId while displaying customer attributes (Name, Email) that are uniquely determined by CustomerId, without requiring extra grouping or artificial aggregation.

Business Value

Better performance practices
Cleaner and more intuitive queries
Improved developer experience
Reduced need for workarounds

02 Mar

Dorine Boudry

MVP

Aggregates become sql. These requirements on the aggregate are caused by the requirements on valid SQL syntax.

Are you proposing an update to how SQL server deals with queries that have a group by clause ?

02 Mar

Sabino Albuquerque Portela

I understand that Aggregates generate SQL and follow valid SQL GROUP BY rules. I’m not proposing any change to SQL Server behavior.

The point is about abstraction and developer experience in OutSystems. In many companies, Advanced SQL is discouraged, and Aggregates are preferred for visual modeling, type safety, and maintainability.

Currently, developers must either add unnecessary fields to the Group By (which may affect performance and granularity) or use artificial aggregations like Max() just to satisfy SQL syntax. This doesn’t reflect the real intent of the query.

The suggestion is for OutSystems to handle this more intelligently at the abstraction layer — for example, by allowing “dependent attributes” or optimizing the generated SQL internally — without changing how SQL itself works.

04 Mar

Dorine Boudry

MVP

Aah, ok.

So hide from the developer the fact that in the underlying SQL, an aggregation function must be used for every desired column ??

Frankly, I still don't really understand what you are asking. How do you see this as an improvement on the performance, in the generated SQL you'd still need to land on an groupBy or an aggregation function for each 'dependent attribute', right?

So would the developer declare an attribute as 'dependent' instead of choosing an aggregation function ?

So is it only about semantics (as it would be the same amount of work)?

04 Mar