Aggregate group not respecting the order by order

Hi,

using service studio 11 on the outsystems cloud (free) :)

I have an aggregate function with two groups and two sums.

The output is not respecting the group by clause nor the order by clause.

This is the output


The aggregate group and sum

This is the sorting on the aggregate


This is what is build the platform


The SQL

It should be group by logdate, domain and not domain,logdate

I know that it would be simpler to write the SQL function, but I want (would like) to write the minimum code as possible, it is my own challenge.


Thank you for your time is reading this long story, few words many pictures :)


Thank you 

Sorry first answer I didn't read something correctly I'll try again.

Group by doesn't respect any particular order. It just puts one line per unique combination so having Domain, Log_Date or Log_date, Domain will give the same result. It will simply add everything up that the two match exactly. Looking at you example it appears to be grouping correctly to me. Can you give an example of what you are expecting as the output.

This is normal SQL behavior and isn't specific to Outsystems, you will get the exact same result if you wrote it directly in SQL




Also for the sort you need to sort by the grouped by columns not the native column. The Group by will have created 2 new fields called just Domain and Log_Date so in the sort you need to select these instead of ByLogDate.Log_Date and ByLogDate.Domain ie just Log_Date not ByLogDate.Log_Date.

Where you are getting confused is that Outsystems by default uses the same name for the grouped field as the original field just without the table name.

Solution

Hi Carl,

You have to use sorting on the calculated date (groupby Data) and table.domain.

To simplify the statement, please see the below illustration:

The dates in the table (Sheet1) is not in order


Now when the data is aggregated and grouped:

you need to sort on 


where LogDate is the computed Group By Attribure and Sheet1.Domain is the table attribute.


Hope this helps,


Regards,

Saugat



Solution

Hi Carl,

Like Suagat said, you have to use grouped attribute in sorting list instead of the entity attribute if you want to order based on groupd values. There is just one correction in what Suagat as suggested that is for Domain also you should choose grouped attribute as you have used group by domain also in your aggrigate.

Hi all, 


many thanks to all, but specially to Saugat, it did work as you said.

I changed the order column to the one without the table name.



Here is the code in oracle with the expected result, just for curiosity


Once again, thanks to everybody who had the time and patience to read. 

Carl