We have reset all users FileMaker related profile fields. Please take the opportunity to update your information,  this will provide background to members whom read your posts. Click here.

Jump to content
Sign in to follow this  
Lem Alpha

Problem with COUNT ( DISTINCT ... )

Recommended Posts

Lem Alpha    6

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

Share this post


Link to post
Share on other sites
Lem Alpha    6

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'...

Share this post


Link to post
Share on other sites
RalphL    2

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Lem Alpha    6

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

×

Important Information

By using this site, you agree to our Guidelines.