Microsoft Dynamics AX (Archived)

How to use count function with order by

(0) Share

Report

Posted on by startax

1,845

Hi all,

I need to print top 7 customer with max Sales Order that means i have to use count function.

I write the following code for get count

while select count(salesid) from salestable groupby salestable.scustaccount

this give me output

CustA 20, CustB30, CustC10, CustD50 etc ....

But i need output something like

CustD50, CustB30, CustA20, CustC10 etc...

http://stackoverflow.com/questions/22475780/how-to-count-and-sort-records-using-axapta-query

According to this post i cant sort aggregate field . Any other option to complete this task.

Any suggestion is very helpful.

Thank You,

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (3)

Verified answer

Mea_ 60,284 on at

Like (0)

Report

Hi Dinkar,

Yes, you cannot use order by and group by same field in AX, as a workaround you can create aggregated view, please check this thread community.dynamics.com/.../594777 where question was about ordering by group by field as well.

Actually same solution (view with aggregated data) was proposed for the question from link provided by you.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

You could create a view the number of orders implemented as a computed column (which can be used for sorting).

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
The other options mentioned work, and another one could be to use Transact-SQL script with an ODBC connection and do something like:

https://arungarg1987.wordpress.com/2011/12/13/connecting-to-databases-through-x/

SELECT TOP 7 CustAccount, count(*) Total FROM SalesTable GROUP BY CustAccount ORDER BY Total DESC

Was this reply helpful? Yes No
Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

Ievgen is right that you can use aggregated functions in a view directly; you don't need computed columns in this case.

I strongly recommend not using direct DB queries if there is a solution native to AX - direct queries are much more difficult to write and debug and it has other problems as well. When it's necessary, I still prefer doing it in a view with computed columns (if applicable).

Was this reply helpful? Yes No
startax 1,845 on at

Like (0)

Report

Hi ievgen,

Thanks for a good suggestion it works but i have one question how do i get top 5 record.

Because i need only top 5 records. Its easy in SQL but any suggestion how do i get only top five record.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

I am afraid you cannot specify that directly in AX, only via the ODBC cursor. You only have firstOnly10 / 100 / 1000, check select statement syntax:

https://msdn.microsoft.com/en-us/library/aa656402.aspx

The solution could be to use a temporary record buffer, and erase the rows that you do not need.

Was this reply helpful? Yes No
Verified answer

startax 1,845 on at

Like (0)

Report

Hi Vilmos,

Thanks for your suggestion but i can use break to get any number of record;

I used counter to get top 5 record and then use break.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

Yes, that works as well indeed.

Was this reply helpful? Yes No