AnsweredAssumed Answered

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

Question asked by LaurentSpielmann on Nov 9, 2012
Latest reply on Apr 4, 2014 by LaurentSpielmann

Summary

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

Product

FileMaker Pro

Version

12v3

Operating system version

Windows 7 Pro

Description of the issue

I'm posting this issue again, because we now have version 12, update 3 with native ExecuteSQL, and the problem is still present.
Original 11v3 issue report can be found here : http://forums.filemaker.com/posts/d981c59865
---------------------------------------------------------

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