1 2 Previous Next 15 Replies Latest reply on May 10, 2016 8:12 AM by beverly

    SQL GROUP BY ISSUE

    MorkAfur

      Summary

      SQL GROUP BY ISSUE

      Product

      FileMaker Pro

      Version

      13 v04

      Operating system version

      Mac 10.10.1

      Description of the issue

      GROUP BY DID NOT WORK AS EXPECTED

      http://forums.filemaker.com/posts/eb7a47380c?commentId=312864#312864

      Steps to reproduce the problem

      http://forums.filemaker.com/posts/eb7a47380c?commentId=312864#312864

      Expected result

      Counts by year.

      Actual result

      "?" in Data Viewer

      http://forums.filemaker.com/posts/eb7a47380c?commentId=312864#312864

      Workaround

      Had to create an extra "helper" count field to use in the SQL.

        • 1. Re: SQL GROUP BY ISSUE
          TSGal

          Mork Afur:

          Thank you for your post.

          The "GROUP BY" clause requires a column name(s).  Therefore, if you want a function performed on a field, then create another field with that function, and then use the GROUP BY clause on that field.  For more information, see page 11 of the FileMaker 13 SQL Reference:

          https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

          TSGal
          FileMaker, Inc.

          • 2. Re: SQL GROUP BY ISSUE
            MorkAfur

            I sincerely appreciate your reply and solving this conundrum.  :)

            I would argue that page 11 should also explicitly say what you said above about any functions done on a GROUP BY expression. Parts of documentation don't always allude to the fact you can use functions even when legal - since it's expected. Therefore, when you have something that's atypical for a SQL GROUP BY expression, it should definitely be documented, explicitly.

            Furthermore, since the GROUP BY with a function around it works fine in MySQL and in Oracle, I was surprised to find this limitation in the otherwise excellent SQL implementation in FMP.

            Yes, creating an intermediate field solved "the problem", but it was a surprising SQL limitation imposed for an unknown reason.

            With the FMP Data Viewer being so limited in usability for quick, easy, or thorough ad-hoc data analysis, I use a separate database console. Then, having to go back into FMP to bridge some SQL gap is again less than as productive as it could (should) be. (Just my two cents..

            Thanks for your reply.

            - m

            • 3. Re: SQL GROUP BY ISSUE
              philmodjunk

              The documentation says "column expression" not "column name".

              • 4. Re: SQL GROUP BY ISSUE
                TSGal

                Mork Afur:

                FileMaker Pro is not a SQL database.  Therefore, what works in MySQL or Oracle may not apply to FileMaker.

                I am open to suggestions on how to make this more explicit.  Currently, it says,

                "The GROUP BY clause specifies the names of one or more fields by which the returned values should be grouped.  This clause is used to return a set of aggregate values.  It has the following format:

                GROUP BY columns

                columns must match the column expression used in the SELECT clause.  A column expression can be one or more field names of the database table separated by commas."

                -----

                What change would you like to propose?  "Functions of columns are not allowed"?  I'll make sure your comments and suggestions are forwarded directly to the manager of Product Documentation.

                As far as enhancements to FileMaker Pro's SQL implementation, be sure to enter this suggestion into our Feature Requests web form at:

                http://www.filemaker.com/company/contact/feature_request.html

                All entries into this web form populate a database file that is hosted and monitored by Product Management and Development.  All entries are read, discussed, and considered for possible implementation in a future release.  Although I could copy your post(s) and paste them into the web form, there are a couple of questions asked on the form that only you can answer.

                TSGal
                FileMaker, Inc.

                • 5. Re: SQL GROUP BY ISSUE
                  philmodjunk

                  What is not clear to me in the documentation is why this SELECT query doesn't work:

                  Select Year ( DateField ) as YR From MyTable Group By YR ...

                  But maybe I am just not clear on what is meant by a "column expression". wink

                  • 6. Re: SQL GROUP BY ISSUE
                    MorkAfur

                    TSGal:

                    I'm not exactly sure what you mean by a "SQL Database", since you have a SQL Reference manual and SQL interface, but whatever.

                    In any case, here is the the answer to your question...

                    Yes, I would add to the SQL Reference that only simple field names are allowed in the GROUP BY and not functions on those fields or something to that effect. Referring to a necessary calculated field to fill the gap should also be documented. Again, since clarification is necessary since this functionality is missing compared with normal and expected SQL functionality.

                    I will leave it up to FM to decide how to improve their product. I've now spent a lot of time leaving forum questions only to find out that there is missing SQL functionality, then being asked to send enhancement requests to fill those gaps. Sigh.

                    Being able to get a simple count by date (or, YEAR, in this case), using SQL, is a simple expectation by any DB developer.

                    Thanks again for your replies.

                    - m

                    • 7. Re: SQL GROUP BY ISSUE
                      TSGal

                      PhilModJunk:

                      The YR alias is referring to a function result of a field; not a field.

                      There is definitely some confusion on this issue, so I have also sent this information to Development and Testing for an explanation.  When I receive any feedback, I will let you know.

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: SQL GROUP BY ISSUE
                        TSGal

                        Mork Afur:

                        Thank you for your comments.

                        Customer suggestions help build a better product.  Leaving suggestions here is fine, but to get the best audience (Product Management and Development - the decision makers), use the Feature Requests web form.  Regardless, I have submitted your comments to Development and Testing for further explanation.  I'll post again when I have more information.

                        TSGal
                        FileMaker, Inc.

                        • 9. Re: SQL GROUP BY ISSUE
                          philmodjunk

                          I understand that YR refers to an expression rather than a field, but the documentation is set up like this:

                          SELECT [DISTINCT] {* | column_expression [[AS] column_alias],...}
                          FROM table_name [table_alias], ...
                          [ WHERE expr1 rel_operator expr2 ]
                          [ GROUP BY {column_expression, ...} ]

                          Note the exact terminology used in both places yet what works in the first is not allowed in the second.

                          • 10. Re: SQL GROUP BY ISSUE
                            MorkAfur

                            Hey Phil,

                            I think that the best case, and the one I'm hoping for, is that this issue is a bug in the SQL Implementation in FMP and that the GROUP BY should do what we both think and expect it should -- allow "expressions", as "documented" on GROUP BY fields (such as GROUP BY YEAR(<field_name>).

                            Worse case is that this is just inconsistent, confusing, incorrect, or poor documentation in the SQL Reference. I agree that saying "expression", but then clarifying it that that just means "fields" implies something's off.

                            I'm anxiously awaiting the reply from the FMP dev team.

                            - m

                            • 11. Re: SQL GROUP BY ISSUE
                              MorkAfur

                              TSGal:

                              Any updates on the GROUP BY issue?

                              Is it a documentation problem (where the docs really need to be specific about needing to create a separate field to help FMP's GROUP BY) or is it an actual bug?

                              As Phil pointed out, although the documentation says "field" for the GROUP BY, it also says expression.

                              I just completed a JDBC program in Java that syncs FMP to MySQL. There is a lot of JDBC code in there that is rather complex so I'm very surprised that something as simple as GROUP BY YEAR(<field_name>) is not possible.

                              Without standard GROUP BY functionality, people just connecting to FMP from remote JDBC clients would thus not be able to do simple SQL queries since they would not have access to the database to add the currently necessary "helper" GROUP BY fields.

                              Hope this situation is clarified in the docs or, better yet, fixed soon so expressions on GROUP BY work as expected.

                              Thanks,

                              -m

                              • 12. Re: SQL GROUP BY ISSUE
                                TSGal

                                Mork Afur:

                                This is definitely a documentation issue.  Development and Documentation are currently working together to find the proper wording.

                                TSGal
                                FileMaker, Inc.

                                • 13. Re: SQL GROUP BY ISSUE
                                  philmodjunk

                                  Perhaps "Column List" should be used with the Group By part of the syntax model instead of "Column Expression".

                                  • 14. Re: SQL GROUP BY ISSUE
                                    TSGal

                                    Mork Afur:

                                     

                                    Page 11 of the FileMaker 15 SQL Reference has been updated to better document the GROUP BY clause.

                                     

                                    https://fmhelp.filemaker.com/docs/15/en/fm15_sql_reference.pdf

                                     

                                    TSGal

                                    FileMaker, Inc.

                                    1 2 Previous Next