Thiis simple code shows how to do a Query using a select statement with GROUP BY clause and Aggregate functions like COUNT, SUM, AVG, etc.. CustTable custTable; while select count(RecId), CustGroup from custTable group by CustGroup order by custTable.RecId desc { info(strFmt("%2 - %1", custTable.RecId, custTable.CustGroup)); }
Category: Group by
AX – D365FO – Multiple Aggregations in X++ Join (Group by, SQL)
CustInvoiceJour custInvoiceJour; CustInvoiceTrans custInvoiceTrans1; CustInvoiceTrans custInvoiceTrans2; while select InvoiceAccount,InvoiceId from custInvoiceJour group by InvoiceAccount outer join InvoiceId, sum(LineAmount) from custInvoiceTrans1 where custInvoiceJour.InvoiceId == custInvoiceTrans1.InvoiceId && custInvoiceTrans1.InvoiceDate >= str2DateDMY(‘1.1.2011′) && custInvoiceTrans1.InvoiceDate <= str2DateDMY(‘31.12.2011′) outer join InvoiceId, sum(LineAmount) from custInvoiceTrans2 where custInvoiceJour.InvoiceId == custInvoiceTrans2.InvoiceId && custInvoiceTrans2.InvoiceDate >= str2DateDMY(‘1.1.2012′) && custInvoiceTrans2.InvoiceDate <= str2DateDMY(‘31.12.2012′) { info( strFmt("Customer:%1 2011:%2 2012:%3", … Continue reading AX – D365FO – Multiple Aggregations in X++ Join (Group by, SQL)
AX / Dynamics 365 For Finance and Operations blog 