Microsoft Dynamics AX (Archived)

While Select statement with sum and group by

(0) Share

Report

Posted on by OrangeRocks

700

Hi,

I have a statement that does not work as expected:

changecompany( "SAM" )
    {
        while select sum( DebitMST ), sum ( CreditMST ),  TransDate, AccountNum, PeriodCode
        from ledgerBalancesDim
        group by ledgerBalancesDim.TransDate

        where ledgerBalancesDim.AccountNum == "777777"  &&           
        ledgerBalancesDim.PeriodCode == PeriodCode::Regular &&
        ( ledgerBalancesDim.TransDate >= mkdate( 01,01,2014 ) && ledgerBalancesDim.TransDate <= mkdate( 31,12,2014 ))
        {
            Do my thing
        }
}

I need to go through records in the view "LedgerBalancesDim" that grouped by date and get the sum of "DebitMST" and "CreditMST".

But the result set limits only to the requirement:

 ledgerBalancesDim.TransDate >= mkdate( 01,01,2014 ) && ledgerBalancesDim.TransDate <= mkdate( 31,12,2014 )

and not to the AccountNum and PeriodCode.

What is wrong with the query?

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

startax 1,845 on at

Like (0)

Report

Hi Pam,

As suggested by @Sukrut use AccountNum, and PeriodCode also in your Group by.

Was this reply helpful? Yes No
OrangeRocks 700 on at

Like (0)

Report

Hi Sukrut and Dinkar, thank you, it worked.

However this behavior seems strange, because I needed to group my records after TransDate only. Can you point me to the information how AX tackles this "group by" command?

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report

Hi,

Please refer below links:

Aggregate Functions: Differences Between X++ and SQL [AX 2012]

msdn.microsoft.com/.../aa677419.aspx

Select Statement Examples [AX 2012]

msdn.microsoft.com/.../aa848113.aspx

Was this reply helpful? Yes No
Suggested answer

Code Work 15 on at

Like (0)

Report

You can try this way in your while select loop

d365code.blogspot.com/.../return-sum-of-any-table-field-using-x.html

Was this reply helpful? Yes No

Community site session details

While Select statement with sum and group by

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

While Select statement with sum and group by

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates