SQL with group by clause not working

(0) Share

Report

Posted on by Community Member

Hi all. I'm trying to create a simple SQL statement in X++,but it's not working.

In regular SQL, it would look like this:

select SUM(PurchQty),SUM(Line Amount),ItemId From purchline group by ItemId.

so I get the summary of quantities group by ItemId.Then my problem is how can I translating it to X++ and have to generate a SSRS report based on this

Thanks

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

please find your solution here

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

Like (0)

Report

I wouldn't accuse the GROUP BY clause for not working. :-)

Regarding your code, it would help if you defined what you mean by "not working". It's difficult for us to give you a solution for "not working".

What you have above looks pretty much as X++ (if I ignore the bug in the name of LineAmount field), so it's again not clear what's your problem regarding "translating it to X++".

I'm not going to discuss report development in a thread about the group by clause, because it's a completely different topic. Let's deal with group by in X++ here; please explain your problem with reports with sufficient details in a separate thread.

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

Like (0)

Report

Sohaib, please explain to us what your link contains and how it answers the question. Linking files without explanation isn't a good practice.

Thank you.

Was this reply helpful? Yes No
Suggested answer

Anton Venter 20,345 Super User 2025 Season 2 on at

Like (1)

Report
Hi, see example below, this query does not have a where clause because it for only for demonstration purpose.

static void AV_SumAndGroupByPurchLine(Args _args) { PurchLine purchLine; while select sum(PurchQty), sum(LineAmount) from purchLine group by ItemId { //some processing here } }

Was this reply helpful? Yes No
Verified answer

Sohaib Cheema 49,438 User Group Leader on at

Like (2)

Report

Martin, I just created a query with two sum fields inside it, grouped by item id, as we can see in below screenshot.

I provided this solution, because I am very much sure that usually at startup, people do face issue in creating these sort of quires for AX-SSRS reports. Specially queries which need sum of fields, along with grouping.

you may download this project, it's simple translating following sql query in ax, also showing view based on that

select SUM(PurchQty),SUM(LineAmount),ItemId From purchline group by ItemId

Was this reply helpful? Yes No
Suggested answer

Luca Pellegrini on at

Like (1)

Report

Hi,

just create an AOT Query and a SSRS report that is using this query.

Hope this can help.

Best regards,

Luca Pellegrini

Was this reply helpful? Yes No