Question about group by

Question about group by

  

Olá.

Alguém sabe se é possível realizar um grupo no campo "Data" nesta tabela abaixo, sem que outros campos (Id, NumberCall e Time) estejam inativos para acesso?

Obrigado.

Hi Letícia,

could you reformulate your question in english so that everyone can help you? thanks :)

Either way, I wonder what you are trying to achieve. If you are grouping by the column Data, what are your expectations with respect to the values of the other columns? 

The group by will "collapse" all rows containing the same value in the Data column to one single row. As the values of the other columns can differ, you need to say how the values should be collapsed. An option is to apply aggregate functions, e.g. MAX, MIN, COUNT, to the other columns. Another option is to include more columns in the group by. See: https://stackoverflow.com/questions/20074562/group-by-without-aggregate-function  

Pedro Rodrigues wrote:

Either way, I wonder what you are trying to achieve. If you are grouping by the column Data, what are your expectations with respect to the values of the other columns? 

The group by will "collapse" all rows containing the same value in the Data column to one single row. As the values of the other columns can differ, you need to say how the values should be collapsed. An option is to apply aggregate functions, e.g. MAX, MIN, COUNT, to the other columns. Another option is to include more columns in the group by. See: https://stackoverflow.com/questions/20074562/group-by-without-aggregate-function  

Hi Pedro,
 
My goal is to display a table with the days (date attribute) on the screen without repeating them.
For example, they are currently like this:


I would like to group them together.
In the first row would display day 2018/02/08 and the attributes "time" (60, 120, 35) and the next row of the table would display day 2018/02/07 and the attribute "time" (30, 20, 40) and so on , you would finalize a data view in the table.

I thought I could deal with GroupBy, but I see that it would not be possible.


Thanks for the answer.



Hi,

Use sql widget.

For Oracle this is the query:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5526018500346202509

regards,

Indra

Hi João/Letícia

Aggregates don't support the kind of requirement you have. As far as I know, there is no standard SQL that will allow you to do it either.

Like Indra pointed out, there may be solutions specific to your database engine, which you'll likely be able to find in an online search or in places like Stack Overflow. Be advised that your application will be bound to that specific database engine.

Alternatively you can split that query in two parts:

  • one that gets all the common part (the different Dates), and use it on a TableRecords, where the second "Time" column would have on it's cell a Web Block that receives the Date as input parameter.
  • As part of the Preparation of the Web Block you would then do the second part of the query (fetch durations of calls for for the input Date) and use something like a ListRecords to display them, comma separated (you have example instructions on one of the exercises of the Developing OutSystems Web Applications online course).

This solution will be database engine agnostic, although a bit more database-intensive, as you will perform several queries to get your desired result. 

Solution

In addition to Jorge's answer, you can perform a single query, sorted by date, and post-process the result in a ForEach, building a new list of Structures with as Attributes the date and a List of values (or another Structure, if you have more values). This is also a typical method to construct a tree-like data structure for e.g. sending to a REST service (or serving a REST call).

What you'd basically do is keeping track of the current date in the list, and if it's the same as the previous, ListAppend the data to the List Attribute of the structure. When you encounter a new date, or when you have processed the last item, you ListAppend the top-level Structure to the list used in the Table Records.

Note that for displaying the items, you either need a Web Block (in case you want to display them as a Table Records of their own), or instead of ListAppending to the List Attribute (as I described above), append them to a Text Attribute instead.

Solution

Kilian Hekhuis wrote:

In addition to Jorge's answer, you can perform a single query, sorted by date, and post-process the result in a ForEach, building a new list of Structures with as Attributes the date and a List of values (or another Structure, if you have more values). This is also a typical method to construct a tree-like data structure for e.g. sending to a REST service (or serving a REST call).

What you'd basically do is keeping track of the current date in the list, and if it's the same as the previous, ListAppend the data to the List Attribute of the structure. When you encounter a new date, or when you have processed the last item, you ListAppend the top-level Structure to the list used in the Table Records.

Note that for displaying the items, you either need a Web Block (in case you want to display them as a Table Records of their own), or instead of ListAppending to the List Attribute (as I described above), append them to a Text Attribute instead.

Hi Kilian,

I did exactly that and it worked.

Thanks


Hi Letícia,

Great to hear it worked. Happy coding :)

Jorge Martins wrote:

Hi João/Letícia

Aggregates don't support the kind of requirement you have. As far as I know, there is no standard SQL that will allow you to do it either.

Like Indra pointed out, there may be solutions specific to your database engine, which you'll likely be able to find in an online search or in places like Stack Overflow. Be advised that your application will be bound to that specific database engine.

Alternatively you can split that query in two parts:

  • one that gets all the common part (the different Dates), and use it on a TableRecords, where the second "Time" column would have on it's cell a Web Block that receives the Date as input parameter.
  • As part of the Preparation of the Web Block you would then do the second part of the query (fetch durations of calls for for the input Date) and use something like a ListRecords to display them, comma separated (you have example instructions on one of the exercises of the Developing OutSystems Web Applications online course).

This solution will be database engine agnostic, although a bit more database-intensive, as you will perform several queries to get your desired result. 

Thank you Jorge for the instructions