group by in query

Hallo, How do I force a “group by” in the visual query? I know that “sort by” is in the Sorting and “where” is in the Range, but how do I get a group by in the query ? thx, Kathleen

You can try like this: TableName var; ; select var group by (FieldName) //Field of the Table where (condition); It should work.

I would like to know how to do that with the AOT (the drag and drop environment) kathleen

hi Kalliet, in the sorting when you add the field which the query will be sort … you can see in the property … that is property name autoheader and autosum … i think that use for groupby hope it’s help :slight_smile: regards, Danang Ismu

Hello, In Querys it seems to be no property for Group By. On the other hand, when you look at Querys in reports. Then you find the property AutoSum used for Group By in reports (Report → Data Source → CustTrans → Sorting → AccountNumber). /Peter Karlsson

hallo Peter, thx … that what i mean … querys in report … sorry for my mistake :stuck_out_tongue: regards, Danang Ismu

Hello, My problem is as followed: I would like to give an overview of the hours worked by the employees (with a total per day) → how can I do that in the sorting of the query i.e. how do I autosumize the amount of hours per transdate? Or do I have to keep track of it manually in the code? thx, Kathleen Alliët

Hi Kathleen, If you use a report, then it’s no problem to get a total per date. Use the datefield as first sortingfield in the query (query on the report), and set the property AutoSum on the sortingfield to Yes. Oh, i hope there is a datefield in your table. If not, then i think you have to use code to sum the transactions. /Peter Karlsson

Hi again, I feel that I must correct my answer [:I]. You CAN group transactions in “standard” querys. Above i said thay you only can group transactions when you use querys in reports. But i just found the way to do it. 1. As sortingfield you use the field you want to group by. 2. The property OrderMode (Data Source → Table) is set to Group by. All done ! In x++ it can look someting like this. queryds.addSortField(fieldnum(CustTrans,AccountNum)); queryds.orderMode(1); /Peter Karlsson

Hi Peter, What I feel, Kathleen wants the ‘Group By’ to be done in MorphX Environment. Using X++ coding, it can be managed in multiple ways … not a big issue. Kathleen did you find any way out? Thanks.

If want to show in a form for the user . Will it be good idea of adding a new index in the table and use that index in the data souurce of the form.So you will get group by automaticaly based on the index order.

Hi, kalliet It is very easy, you can reference the report tutorial_SumPerSubFieldDate, and you can find it in AOT/Report. wish this help you.

look at this code: (OrderMode) q = new query(salesTable_DS.queryRun().query()); q.dataSourceTable(tablenum(salesLine)).addSelectionField(fieldnum(salesLine, lineamount), SelectionField::Sum); q.dataSourceTable(tablenum(salesLine)).addSortField(fieldnum(salesline, salesId)); q.dataSourceTable(tablenum(salesLine)).addSortField(fieldnum(salesline, dataAreaId)); q.dataSourceTable(tablenum(salesLine)).orderMode(OrderMode::GroupBy); queryRun = new queryRun(q);