Finance | Project Operations, Human Resources, ...

Select Count(Distinct field) using Group By

(0) Share

Report

Posted on by Cody M

Hi All,

I'm trying to get the count of distinct quality order IDs in InventNonConformanceOrigin, where the quality order ID also exists in the InventQualityOrderTable:

        select count(RecId) from inventNonConformanceOrigin
            group by inventNonConformanceOrigin.InventQualityOrderId
            join inventQualityOrderTable
            where inventNonConformanceOrigin.InventQualityOrderId == inventQualityOrderTable.QualityOrderId;

The SQL query for it is:

SELECT COUNT(DISTINCT INVENTQUALITYORDERID) FROM INVENTNONCONFORMANCEORIGIN
INNER JOIN INVENTQUALITYORDERTABLE ON INVENTNONCONFORMANCEORIGIN.INVENTQUALITYORDERID = INVENTQUALITYORDERTABLE.QUALITYORDERID

The issue I'm have is that this just returns a value of 1 in inventNonConformanceOrigin.RecId. It should be returning over a thousand.

I have a similar while select query that selects distinct records using group by without any issues. It pulls all the records as expected:

        while select InventQualityOrderId from inventNonConformanceOrigin
            group by inventNonConformanceOrigin.InventQualityOrderId
            join inventQualityOrderTable
            where inventNonConformanceOrigin.InventQualityOrderId == inventQualityOrderTable.QualityOrderId
        {
            //
        }

Does anyone know what I'm missing with using the group by, or is there an alternate way to get the count I'm after? I've searched around and looked in this forum, but I wasn't able to find an existing post about this scenario. Any help is greatly appreciated, thank you!

I have the same question (0)

All responses (2)

Answers (0)

Martin Dráb 239,022 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

What you claim to be the SQL query for the X++ code is actually a completely different query. You forget to include the "GROUP BY" clause and that's also a reason why you get just a single value. Your SQL code counts all qualifying records; it doesn't count records per order ID.

Also, another obvious difference between those two queries is that one counts distinct values only and the other counts all.

Was this reply helpful? Yes No
Suggested answer

Cody M 55 on at

Like (0)

Report
Copy link

Link copied!
The SQL query doesn't have the group by because it has distinct, and I read that for x there was no distinct keyword, so it was recommended to use group by in x to get distinct records.

I found another post after I posted that explained it better. I just ended up putting a counter in a while select, like mentioned in the responses to this post:
https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/151046/select-count-distinct-xx?pifragment-109373=2#responses

while select RecId from inventNonConformanceOrigin group by inventNonConformanceOrigin.InventQualityOrderId join inventQualityOrderTable where inventNonConformanceOrigin.InventQualityOrderId == inventQualityOrderTable.QualityOrderId { counter ; }

Was this reply helpful? Yes No