5 Replies Latest reply on Apr 4, 2014 4:46 AM by LaurentSpielmann

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

    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

        • 1. Re: Internal SQL with plugin : Problem with COUNT ( DISTINCT ... )
          TSGal

          Laurent Spielmann:

          Thank you for your post.

          This issue was already reported by another customer (not on the forum).  The customer worked around the problem by not using DISTINCT since the GROUP-BY clause automatically makes the column distinct for aggregate functions (Count in this case).  Can you confirm?

          Regardless, I have attached your post to the original issue.  If any additional information becomes available, I will let you know.

          TSGal

          FileMaker, Inc.
           

          • 2. Re: Internal SQL with plugin : Problem with COUNT ( DISTINCT ... )
            LaurentSpielmann

             Hi TSGal, and thanks for your quick answer.

            Actually, there's a slight misunderstanding here : the DISTINCT I'm referring to is not associated with SELECT (in which case it would indeed be redundant with GROUP BY), but with COUNT, so omitting it would simply not return the result I'm looking for :

            GROUP BY results in having only 1 row per distinct Teacher, corresponding to a set of records in te DB.

            A simple COUNT ( Student ) would then return the amount of records for this Teacher
            (as would COUNT ( any non NULL field ) )

            What I'm using here, is COUNT ( DISTINCT Student ) to retrieve how many distinct students are showing for this Teacher.

            If you take a look at the attached file, it may become clearer.

            Regards

            Laurent

            • 3. Re: Internal SQL with plugin : Problem with COUNT ( DISTINCT ... )
              TSGal

              Laurent Spielmann:

              Thanks for the clarification.  I have attached your comments along with the sample file to the original issue.  Again, I'll post again when I receive a response.

              TSGal
              FileMaker, Inc.

              • 4. Re: Internal SQL with plugin : Problem with COUNT ( DISTINCT ... )

                     I have also run into this problem.  I have used DISTINCT inside of a COUNT () clause, and I am getting results that are 1 less than they should be.  This is on 12.04 FMPA, using ExecuteSQL() internal function.

                     A::ID = B.ID_FK

                     A fields:  ID, Type

                     B fields:  Part_ID, ID_FK, Worker, Date

                     Table B has records for unique parts that were worked on; each part belongs to a single parent (a record in A), and each parent is of a certain Type. 

                     ExecuteSQL (
                         "SELECT B.Worker, A.Type ,  Count ( DISTINCT B.ID_FK )
                         FROM A
                         INNER JOIN B ON B.ID_FK = A.ID
                         WHERE B.Date > ?   AND   B.Date < ?
                         GROUP BY B.Worker, A.Type, B.ID_FK
                         ORDER BY B.Worker, A.Type"
                     ; "    " ; "" ; Globals::Date_Start ; Globals::Date_End )

                     It appears that the problem may have to do with the number of records returned that need to be summarized.  I looked at my data and the groups that had multiple records of one type were correctly counted as 1.  And all Type-groups that were miscounted contained at least 1 Type that had only 1 part.  Oddly, not all groups that had a Type-group with only 1 part in it was miscounted; most of them were, but not always.

                     I tried rewriting the SQL such that the DISTINCT wasn't included, but I wasn't getting the right results.  (Not that the results themselves were inaccurate, but they weren't the results/summaries that I wanted.)

                     --  J

                • 5. Re: Internal SQL with plugin : Problem with COUNT ( DISTINCT ... )
                  LaurentSpielmann

                       Hi everybody,

                       the problem is finally solved with update 13.0 v2 !

                       Thanks to you all.