Hello All,

i have the below table with a lot of columns . One of this named "FormId" and i want to create an sql query with group byt the FormId column and to preview them to user.


I have create the below sql but i something happened wrong (maybe by me)


Your support is highly appreciated.

Hi George,

1) You don't need to use a SQL query, your query is easy enough to use an Aggregate for that.

2) You don't seem to understand what GROUP BY does: this is standard SQL, so not OutSystems-related!

As for your error: if you have a GROUP BY, you can only SELECT the GROUPed BY columns, and aggregate functions (like SUM, COUNT, MAX, MIN etc.). But again, this is bog-standard SQL, and has nothing to do with OutSystems.


Helo Kilian,

sorry but i can not aggregate , the option is not enabled.

Any advice/suggestion?

Hello George,

When you use group by, only grouped attributes will be available to the SELECT.

The same happens with the Aggregate. If you need the Id, you need to Group By it by the ID.
It will work the same way on both SQL and Aggregate.

Cheers.

Hi George,

Why do you think you need a GROUP BY, while you don't seen to understand what it does? Someone told you to use it?

Hi George,

When you want to select a column with GROUP BY, you should either GROUP BY this column or use a aggregate function for this column (like SUM(),MAX() etc.).

This is a SQL standard. You may need to learn some T-SQL, i think this may help you a lot.

Anyway, good day.

Kilian Hekhuis wrote:

Hi George,

Why do you think you need a GROUP BY, while you don't seen to understand what it does? Someone told you to use it?

Dont focus to the reason, it is business need :) . I refer to Group By because as user can group the table  and preview the required result, but the point is to create an sql query in order to preview to the user.


Hello Again,

improving the syntax i get the below error. Can you help pls?


George,

I recommend you to read this. I did it in order to help people to make questions in the forum.

It seems, from your question and fix tentatives, that you do not dominate SQL. You should read something about it (the basics, for start, as you are having problems with the syntax). There are a number of free online resources you can use to start.

Also, it seems you want to do something "dynamic"? Like a pivot table? 

If it is so, you will not be able to do it with "normal code", I think. In order to show data in a way the user can manipulate it in the page, you will need something much more advanced.

I recommend you to take a look at the Forge for some already made component that can provide it (Data Grid, etc). But I am afraid you will have trouble to implement.

Cheers!

George Mitsiou wrote:

Dont focus to the reason, it is business need :) . I refer to Group By because as user can group the table  and preview the required result, but the point is to create an sql query in order to preview to the user.

A GROUP BY is a technical solution of a technical problem, never a "business need". Since you clearly don't understand what it does, how can you say it's needed?

Also, what do you mean by "[a] user can group the table"? What kind of "preview" are you talking about?


Let's explain then the need.

I have a table named "Customers". This table includes the below attributes:

1. ID (the unique record ID)

2. Surname (type text)

3. Name (type text)

4. Hobbies (type Text)

Note that every customer has unique ID, Surname and Name but maybe more than one hobbies. 

For example, now in my table the records displayed as the below screen

The need is my table to display the records with the below structure


Your support is highly appreciated. 

Hi,

The easiest (not more "performatic") to do it is like this:

1. Aggregate: Your Table, grouped by Id, Surname and Name.
2. Table Records using (1) as its source. Show only those three attributes, but add an extra column to the Table Records.

Create a Block that receives as input parameter the ID.
If this is a Traditional App, add a Preparation to the Block. Create a new aggregate, filtered by the ID. So, only records for this ID will be present in the result.
Use it as a source to a List, where you show the Hobbies.

Drag and drop your block to the Table records in the page, and pass the ID of the current record of the list of the Table Records to the Block parameter (e.g. YourTable.List.Current.Id)

Using SQL you can improve performance, but it will depend on the dialect of the database you are using (like T-SQL), and if you do not dominate SQL, I don't recommend.

Cheers.

Hello,

thanks for your reply.

I follow successfully the steps 1 & 2. Unfortunately i dont get you with the following

Create a Block that receives as input parameter the ID.
If this is a Traditional App, add a Preparation to the Block. Create a new aggregate, filtered by the ID. So, only records for this ID will be present in the result.
Use it as a source to a List, where you show the Hobbies.

Drag and drop your block to the Table records in the page, and pass the ID of the current record of the list of the Table Records to the Block parameter (e.g. YourTable.List.Current.Id)

Can you provide a mini guide with screens please? 

Also you refer "filtered by the ID" . Can you share a example filter template?


Thanks in advance.