Thank you for your post.
This issue was already reported by another customer (not on the forum). The customer worked around the problem by not using DISTINCT since the GROUP-BY clause automatically makes the column distinct for aggregate functions (Count in this case). Can you confirm?
Regardless, I have attached your post to the original issue. If any additional information becomes available, I will let you know.
Hi TSGal, and thanks for your quick answer.
Actually, there's a slight misunderstanding here : the DISTINCT I'm referring to is not associated with SELECT (in which case it would indeed be redundant with GROUP BY), but with COUNT, so omitting it would simply not return the result I'm looking for :
GROUP BY results in having only 1 row per distinct Teacher, corresponding to a set of records in te DB.
A simple COUNT ( Student ) would then return the amount of records for this Teacher
(as would COUNT ( any non NULL field ) )
What I'm using here, is COUNT ( DISTINCT Student ) to retrieve how many distinct students are showing for this Teacher.
If you take a look at the attached file, it may become clearer.
Thanks for the clarification. I have attached your comments along with the sample file to the original issue. Again, I'll post again when I receive a response.
I have also run into this problem. I have used DISTINCT inside of a COUNT () clause, and I am getting results that are 1 less than they should be. This is on 12.04 FMPA, using ExecuteSQL() internal function.
A::ID = B.ID_FK
A fields: ID, Type
B fields: Part_ID, ID_FK, Worker, Date
Table B has records for unique parts that were worked on; each part belongs to a single parent (a record in A), and each parent is of a certain Type.
"SELECT B.Worker, A.Type , Count ( DISTINCT B.ID_FK )
INNER JOIN B ON B.ID_FK = A.ID
WHERE B.Date > ? AND B.Date < ?
GROUP BY B.Worker, A.Type, B.ID_FK
ORDER BY B.Worker, A.Type"
; " " ; "" ; Globals::Date_Start ; Globals::Date_End )
It appears that the problem may have to do with the number of records returned that need to be summarized. I looked at my data and the groups that had multiple records of one type were correctly counted as 1. And all Type-groups that were miscounted contained at least 1 Type that had only 1 part. Oddly, not all groups that had a Type-group with only 1 part in it was miscounted; most of them were, but not always.
I tried rewriting the SQL such that the DISTINCT wasn't included, but I wasn't getting the right results. (Not that the results themselves were inaccurate, but they weren't the results/summaries that I wanted.)
the problem is finally solved with update 13.0 v2 !
Thanks to you all.