AX query - sorting with sum field

(0) Share

Report

Posted on by Sona Jee

436

Hi all,

I am trying to sort by a sumation of field, but its not working.
My sql query is working okay, but X++ not
SQL -

select  sum(NETAMOUNT),CUSTACCOUNT
from RETAILTRANSACTIONTABLE
group by CUSTACCOUNT
order by sum(NETAMOUNT) desc

AX query -

            qbds.addGroupByField(fieldNum(RetailTransactionTable, CustAccount));   
            qbds.addSelectionField(fieldNum(RetailTransactionTable,netAmount),SelectionField::Sum);  
            qbds.addSortField(fieldNum(RetailTransactionTable,netAmount),SortOrder::Descending);

Suggest me how to achieve this.

Thanks

*This post is locked for comments

I have the same question (0)

All responses (14)

Answers (0)

Suggested answer

udaY-ch 5,133 on at

Like (0)

Report

Hi,

Can you show us the full query. Hope you had done it well.

axspecialist.wordpress.com/.../query-with-aggregate-function

Best,

Uday.

Was this reply helpful? Yes No
Suggested answer

Rustem Galiamov 8,072 on at

Like (0)

Report
Hi Sona Jee!

Try this:

qbds.addGroupByField(fieldNum(RetailTransactionTable, CustAccount)); qbds.addGroupByField(fieldNum(RetailTransactionTable, netAmount),SelectionField::Sum); qbds.addSortField(fieldNum(RetailTransactionTable, netAmount),SortOrder::Descending);

i.e, change addSelectionField to addGroupByField

Was this reply helpful? Yes No
Rustem Galiamov 8,072 on at

Like (0)

Report

Ahh..Sorry. You need one total sum to every customer.

Was this reply helpful? Yes No
Sona Jee 436 on at

Like (0)

Report

Hi Rustem Galiamov,

I tried with addGroupByField , it didn't worked.

Please suggest me another option.

Was this reply helpful? Yes No
Rustem Galiamov 8,072 on at

Like (0)

Report

This query for lookup or something else?

Was this reply helpful? Yes No
Sona Jee 436 on at

Like (0)

Report

This query for filter top customer account based on sum of netAmount and order by descending.

Was this reply helpful? Yes No
Suggested answer

Rustem Galiamov 8,072 on at

Like (0)

Report

I think the best way is to create a view with customers and total netAmount by customer and then use this view in your query.

Was this reply helpful? Yes No
Keshav Kumar 220 on at

Like (0)

Report

Hi

I think your sequence is incorrect.

Try this

qbds.addSelectionField(fieldNum(RetailTransactionTable,netAmount),SelectionField::Sum);

qbds.addGroupByField(fieldNum(RetailTransactionTable, CustAccount));

qbds.addSortField(fieldNum(RetailTransactionTable,netAmount),SortOrder::Descending);

Was this reply helpful? Yes No
Sona Jee 436 on at

Like (0)

Report

Hi Keshav Kumar

It didn't worked.

Was this reply helpful? Yes No
Suggested answer

Rustem Galiamov 8,072 on at

Like (0)

Report

You can't do it like in SQL. Try to create view as i mentioned earlier.

Was this reply helpful? Yes No