AnsweredAssumed Answered

Internal SQL : Problem with COUNT ( DISTINCT ... )

Question asked by LaurentSpielmann on Dec 12, 2013
Latest reply on Apr 4, 2014 by LaurentSpielmann

Summary

Internal SQL : Problem with COUNT ( DISTINCT ... )

Product

FileMaker Pro

Version

13

Operating system version

Windows 7 Pro

Description of the issue

I'm sorry to bring this up again, but the problem is still present in v13...
Original 11v3 issue report can be found here : http://forums.filemaker.com/posts/d981c59865
v12 issue here : http://forums.filemaker.com/posts/4d4bb0da3e
---------------------------------------------------------

I'm getting strange results using "distinct" in an agregate Count in the following query :
SELECT T1.Teacher, COUNT(DISTINCT T1.Student), SUM(T1.Hours)
FROM T1
GROUP BY T1.Teacher
this returns, with a pipe separator and spaces added here for clarity :
TEACHER C | 11 | ...

it should be : TEACHER C | 12 | ...

Steps to reproduce the problem

You can dowload a sample file here :
https://dl.dropbox.com/u/11429829/SQL_COUNT_DISTINCT_v12.zip
Works with native ExecuteSQL, MyFMbutler DoSQL and 2emPowerFM SQL Runner.

Or create a table T1 with at least 2 fields :
Teacher and Student

and run the given query with native ExecuteSQL or any SQL plugin.

if, for a given Teacher, there's only 1 record for the first student, the calculation for the distinct count of students will be wrong. If there's more than one, it will be correct

(see sample file)

Expected result

TEACHER C | 12 | ...

Actual result

TEACHER C | 11 | ...

Exact text of any error message(s) that appear

no error message

Configuration information

If used with v12 native ExecuteSQL function, nothing special.
If not, requires a plugin for internal SQL queries
(like MyFMbutler DoSQL or 2emPowerFM SQL Runner)

Workaround

none

Outcomes