2 Replies Latest reply on Apr 4, 2014 4:45 AM by LaurentSpielmann

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

    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