Grouped Aggregates not allowing access to other columns

Grouped Aggregates not allowing access to other columns


Hi All,

This is my first post here as I have just started to build a new web app for my company.

I have a big problem, however, when it comes to the Aggregate.

I need to group all records on my schedule by time, then by instructor - this is easy to do in the aggregate window. However, when I try to pull other data out in my ListRecord, such as client names or class levels, I am unable to as the other columns are inaccessible and greyed out. I don't want to have to group every single column I need to get my information - is there a way around this or am I doing something wrong?

Many thanks in advance.


hi Michael,

In this case, you need to learn about analytical function, you can use SQL Advanced widget. 

For Oracle function read this:



Hi Michael,

That's standard behaviour also in regular SQL queries... only GROUPed columns will be available to be SELECTed. This is not even an OutSystems issue, it's by design, for relational databases.

I've never used Oracle Analytic functions, they might do the trick and as far as I can tell they exist for both SQL Server and Oracle (maybe different syntaxes?) so you can use them if you're using the SQL query tool.

That being said, can you further explain what's your goal with this? There might be other commonly used approaches in OutSystems to it already.



Now consider the analytic function query (Query-2) and its result. Note the repeating values of DEPT_COUNT column.

This brings out the main difference between aggregate and analytic functions. Though analytic functions give aggregate result they do not group the result set. They return the group value multiple times with each record. As such any other non-"group by" column or expression can be present in the select clause, for example, the column EMPNO in Query-2.




Hi all,

Thanks for your replies.

A bi more about what I am trying to achieve.

We have a schedule of classes during the day. I would like to group the classes first by TIME so all classes at, say, 9am are listed underneath the 9am heading, then grouped by instructor, then list the students in that class. Eventually I would like to create a visual daily schedule broken up into instructors, but that will be down the track.

I have looked at nesting ListRecords by cannot see how I can achieve this, seeing as though the web blocks that are nested are not scoping to the parent.



hi, Michael:

play with sql analytic and it is fun (i try in Cloud):





I'd suggest you do some logic work there:

  1. instead of grouping by, sort by those columns, so that records are in the order you will want to display them.
  2. then process the records and build your own list of structures that reflect the way you want to display them on screen

I've just given a short example of how to do this sort of thing in another scenario (floors with rooms) that you can find here.

You could use an advanced query with a subquery, or split your aggregate into 2 (or more).