Problem with COUNT ( DISTINCT ... )

[Lem Alpha]

Hello there,

I'm getting strange results using "distinct" in an agregate Count in the following query :

SELECT s.Zkf_Formateur, li.Zkf_Groupe, COUNT(DISTINCT li.Zkf_Contact)

FROM LISE__ AS li

JOIN SEAN__ AS s

ON li.Zkf_Seance=s.Zkp

WHERE li.Zkf_Groupe='GROUCF63457729200OF90YL24'

AND s.Zkf_Formateur='CONT63394136111XUYM50SA'

GROUP BY  s.Zkf_Formateur, li.Zkf_Groupe

this returns, with a pipe separator and spaces added here for clarity :

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | 11



This should return

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | 12

as does the same request with just COUNT(li.Zkf_Contact).



As a proof, here is the result of the query with no agregation. the li.Zkf_Contact column shows 12 distinct values :

SELECT s.Zkf_Formateur, li.Zkf_Groupe, li.Zkf_Contact

FROM LISE__ AS li

JOIN SEAN__ AS s

ON li.Zkf_Seance=s.Zkp

WHERE li.Zkf_Groupe='GROUCF63457729200OF90YL24'

AND s.Zkf_Formateur='CONT63394136111XUYM50SA'

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT63457835431ZLV16T9Z

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT634578354311S9V0QB9

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT63457835430VD51X3E1

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT63457835430RR1B7S51

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT63457835430FTDSF16C

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT63457835430OO09T4P9

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT634578354301M3GKDA1

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT63457835430383HDB8F

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT63457835430XJJQ60DZ

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT634528279168RIKDYJS

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT63452479959YV4FIMH8

CONT63394136111XUYM50SA | GROUCF63457729200OF90YL24 | CONT634524799598J803ZA2

Now, I may not be (and am not !) an SQL expert, but there's something here I really can't figure out...

Anyone having an idea ?

Thanks a lot

[Lem Alpha]

Oh, and I forgot to mention that :

1) with another couple of li.Zkf_Groupe and s.Zkf_Formateur, it works (although there are many more rows, the count (distinct) returns the correct result

2) on this very same query, there's actually another field containing 3 for each row, and I ask for a fourth column in the query : SUM ( ... )

Well the result is alwas correct (36), even with the query that show the wrong 'count'...

[Lem Alpha]

Here is a sample file with simplified data.

Works with MyFMbutler DoSQL and 2emPowerFM SQL Runner.

See the 12 last records, for "Teacher C" to understand the problem.

SQL_COUNT_DISTINCT.zip

[RalphL]

I think you have found a bug in the SQL engine. Teacher C has no duplicate Students, change one of the students or add another to make a duplicate and it works. I also added Teacher D and had the same results.

[Lem Alpha]

Hi Ralph,

yes, so it seems...

I did report it to Filemaker yesterday.

Thanks for your comment.

[Peter Wagemans]

Hi Laurent,

More awake now. I fiddled with the records to see when and where things go wrong.

The first record of the grouping is not counted with COUNT ( DISTINCT.. ). I tried

mFMb_DoSQL ( "

SELECT T1.Teacher, COUNT(DISTINCT T1.Student)

FROM T1

WHERE T1.Teacher = 'TEACHER X'

GROUP BY T1.Teacher" )

And of course this returns a blank.

Then I change the first teacher to "TEACHER X" and it gives me

TEACHER X,0

When I remove the DISTINCT part, it returns 1 instead of 0.

The problem seems to be in the COUNT() function.

Confirmed on my part.

[Lem Alpha]

Hi Peter,

thanks so much for the fiddling !

It was also confirmed by Chris (SQLRunner at Dracoventions) in an e-mail chat, and strongly suspected by people at FM answering my issue report.

I guess that settles it... Unfortunately, I have some rather complex excel reporting to do by tomorrow (of course), that's why I tried to get confirmation asap.

I'd rather have been given some "Your sql call is not correct" ...

So I'll have to do it the old fashioned and native way !

Again, thanks to you all.

[Lem Alpha]

Just a little follow-up, for those interested : still the same problem in 12v3 and native ExecuteSQL.

 

Updated issue report can be found here : http://forums.filemaker.com/posts/4d4bb0da3e