AnsweredAssumed Answered

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

Question asked by LaurentSpielmann on Mar 12, 2012
Latest reply on Apr 4, 2014 by LaurentSpielmann

Summary

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

Product

FileMaker Pro

Version

11v3

Operating system version

Windows 7 Pro

Description of the issue

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 :
http://dl.dropbox.com/u/11429829/SQL_COUNT_DISTINCT.zip
Works with MyFMbutler DoSQL and 2emPowerFM SQL Runner, so it's not related to the plugin.

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

and run the given query with 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

requires a plugin for internal SQL queries
(like MyFMbutler DoSQL or 2emPowerFM SQL Runner)

Workaround

none

Outcomes