3 Replies Latest reply on Apr 4, 2014 4:47 AM by LaurentSpielmann

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



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


      FileMaker Pro



      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 :
      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)