1 2 Previous Next 23 Replies Latest reply on Jun 10, 2017 4:01 AM by siplus

    Query with both grouped and ungrouped fields

    texas0478

      Hi,

       

      I have a very simple question on a querying method, incl. only one table:

      Say I've got this table as follows:

       

      Name     City               Age     NumberOfDogs

      -----------------------------------------------------

      John       New York          40                    5

      Rita           Boston            26                   3

      John       New York          42                    2

      Rita          Sydney            30                    1

      John         Boston            41                    2

       

      I would like to design a query which

      - finds the oldest person among the ones sharing the same first name and living in the same
      city AND

      - displays how many dogs this person owns

       

      Therefore the query should provide the following results:

      Name     City               Age     NumberOfDogs

      -----------------------------------------------------

      Rita           Boston            26                   3

      John       New York          42                    2

      Rita          Sydney            30                    1

      John          Boston           41                    2

       

      I've been experimenting with self-join relationships and SQL subqueries but was unable to make it...

      Either an SQL implementation or a TO model would be much appreciated.

       

      Thanks

      Gergő

       

      PS. Greets from the sunny Budapest

        • 1. Re: Query with both grouped and ungrouped fields
          beverly

          Can you post your Query attempt(s)?

          Beverly

          • 2. Re: Query with both grouped and ungrouped fields
            philmodjunk

            Generally speaking: Criteria that specifies "largest", "first", "smallest"... Values that identify the desired record only relative to the other records in the table, is accomplished by using a sort of all records matching the other criteria used in your query, then selecting the first (or last) record from the sorted set or group. Exactly which of several methods that you might use to do that depends on layout design and query method.

            • 3. Re: Query with both grouped and ungrouped fields
              texas0478

              Sure. I tried various SQL modes, and although the subqueries seemed to be the right path, they only work in Filemaker if they are in the WHERE part of the SQL code.  Anyway, my original concept was this:

               

              SELECT Name, City, max(Age), max(NumberOfDogs) FROM Table

              GROUP BY

              Name, City

               

              The result set the query yields is this:

               

              Name     City               Age     NumberOfDogs

              -----------------------------------------------------

              Rita           Boston            26                   3

              John       New York          42                    5

              Rita          Sydney            30                    1

              John         Boston            41                    2

               

              Unfortunately the NumberOfDogs columns displays inappropriate results, as John (NYC) has 2 dogs.  However, if the MAX function is omitted from the SELECT clause, the code fails to execute because of the grouping.

               

              Thx for any suggestion in advance,

              Gergő

              • 4. Re: Query with both grouped and ungrouped fields
                texas0478

                Very true. Coming from MS Access I believe I would have tried the first() / last() functions, but they don't exist in Filemaker.

                 

                Actually my original tables are more complicated and I used this demonstrative example of one single table so that they are easy-to-digest and the pro people here could write a quick SQL or TO hint for me that I can later scale up and implement in my solution.  As said, either of the two querying methods would be good for me.

                 

                Thanks,
                Gergő

                • 5. Re: Query with both grouped and ungrouped fields
                  philmodjunk

                  We have equivalent methods--one of which uses a function named "last". Go to record/request/page [first,last...], GetValue, And Fetch First Row Only, all might be used to do that.

                   

                  As I said before, it depends on your query method--something that you have not described yet in this discussion.

                   

                  In general you can query data in FileMaker:

                   

                  1. By performing a find and then (if necessary) sorting the results
                  2. By setting up a relationship to the data where the criteria control what records match. A portal or Go TO Related Records can be used to bring up a list of records satisfying the query criteria
                  3. By using both a relationship and a filtered portal
                  4. By using the ExecuteSQL function. (results can be listed in a single field or you can get a list of primary keys to use to quickly get a found set of such records.)
                  • 6. Re: Query with both grouped and ungrouped fields
                    texas0478

                    Hi,

                    Thank you, more or less I am aware of the concepts you described.

                     

                    Here I specifically would like to

                    a) group records by certain fields

                    b) choose the last record in the group and query its othe fileds (in my example: the number of dogs)

                     

                    The functions you have described are either not for use in SQL (Go to record, GetValue) or serve a different purpose.  FETCH unfortunately fetches the first row of the complete result set, not the first row of each group.

                     

                    Any specific help on the original problem is appreciated,

                    Kind Regards

                    Gergő

                    • 7. Re: Query with both grouped and ungrouped fields
                      beverly

                      I tried with your data and using the

                      HAVING

                      clause. But that did not work either.

                      Sub-selects are not always helpful with ExecuteSQL.

                      Perhaps sorting and looping the list (scripted) will help you walk the rows and return want you want?

                      Beverly

                      • 8. Re: Query with both grouped and ungrouped fields
                        coherentkris

                        Just like BEV I was thinking that a scripted solution might be easiest...

                        • 9. Re: Query with both grouped and ungrouped fields
                          texas0478

                          Hi,

                          Thanks a lot for your try!  Literally a sub-query should do the job, but it should be referenced from the FROM clause, which the FM SQL engine does not allow.

                          Scripting could be one option, or – what I am currently working on – is two SQL commands subsequent to each other, where the first is a make table query (using the epSQLexecute plugin-feature).

                          Thank again.

                          Gergő

                          • 10. Re: Query with both grouped and ungrouped fields
                            philmodjunk

                            Set up a self join

                             

                            YourTable::FirstName = YourTable 2::FirstName AND

                            YourTable::City = YourTable 2::City

                             

                            Double click the relationship line and sort YourTable 2 by Age in descending order.

                             

                            On a layout based on YourTable, You can put any fields from YourTable 2, such as Dogs, to show data for the oldest person with the same name and city.

                             

                            In a sub summary report where each group of records with the same name and city is condensed into a single row by setting up a sub summary layout part in place of the body, you'd get the table that you have shown in your initial post.

                            • 11. Re: Query with both grouped and ungrouped fields
                              philmodjunk

                              Just as a side comment. Experienced developers used to SQL based systems frequently encounter this particular bit of frustration. You know the result that you want, but when you reach into your tool box for that familiar SQL tool for getting what you want, you find that it won't work in FileMaker and you have to use other means to the same end.

                               

                              In general, FileMaker's "Query by Example" design makes very simple queries very easy and user friendly at the cost of making more complex queries such as this one more complex and difficult than you would find in a true SQL based DB.

                              • 12. Re: Query with both grouped and ungrouped fields
                                texas0478

                                Hi,

                                 

                                Thank you very much for your answer.  Stupid me, I tried the self-join relationship, but only in SQL, where I faced the mentioned limitations.

                                Combining the TO self-join with the reporting view's grouping is a very nice solution.  Am I correct, that such sub-summaries are also available in table view, therefore an Excel exportable table can also be produced this way?

                                 

                                Thanks again, have a nice day

                                Gergő

                                • 13. Re: Query with both grouped and ungrouped fields
                                  philmodjunk

                                  You won't need table view to export this to excel. Just use the "group by" option to export one row of data for every group of records produced by your sorting.

                                  • 14. Re: Query with both grouped and ungrouped fields
                                    siplus

                                    Here is another solution.

                                     

                                    It puts the result in a text field so you can copy paste in Excel.

                                     

                                    I'm using a script but could have as well used a calc.

                                    1 2 Previous Next