3 Replies Latest reply on Oct 29, 2014 9:18 AM by philmodjunk

    Execute SQL Group By

    philmcgeehan

      Title

      Execute SQL Group By

      Post

      Hello

      I'd like to get a list returned with a calculated date field and a summary field using SQL.

      So far I can get the dates on their own using this ExecuteSQL command:

                                                                       
      ExecuteSQL ( "
                         SELECT \"c_MonthYear\"
                         FROM \"WeeklyWork_DASH\"
                         GROUP BY \"c_MonthYear\"
                     " ; ",  "; "" )
                     

      2014-01-01
                     2014-02-01
                     2014-03-01
                     2014-04-01
                     2014-05-01
                     ...

                     

       

       

       

       

       

      But when I try to add a summary field as well I get a "?" returned.

                                                                       
      ExecuteSQL ( "
                         SELECT \"c_MonthYear\", \"s_DashUnits\"
                         FROM \"WeeklyWork_DASH\"
                         GROUP BY \"c_MonthYear\"
                     " ; ",  "; "" )
      ?                    

       

       

       

       

      Also, If I just try to get the s_DashUnits I also get a "?"

      Hosted on FMS13
      The majority of my users have FMP13, but a couple have FMP12A.

      Does anybody know if I'll be able to get this to work... and how to?

      Thanks

        • 1. Re: Execute SQL Group By
          philmodjunk

          I don't think referring to a summary field will work here. Try a function call with the field being summarized in place of the summary field. Such as Sum ( SummarizedField ) or Count ( SummarizedField ).

          Summary fields require a specific context in which to evaluate and I doubt that a workable context is available when you reference a field of type summary using SQL.

          • 2. Re: Execute SQL Group By
            philmcgeehan

            Excellent, that works just how I wanted.

            Thanks Phil.

            • 3. Re: Execute SQL Group By
              philmodjunk

              Some "Known Contexts" in which a summary field can evaluate to produce an aggregate value:

              A "non Running" Summary field produces an aggregate value (a value from more than one field in one record). The value returned is determined by the context in which it is used/displayed:

              Summary field is referenced on a layout based on the table in which it was defined:

              A group within a FoundSet

              If you place the summary field in a subsummary part that specifies the "break" field that grouped the records when the found set was sorted, you get a subtotal--the total for that group.

              In a calculation, you can use the getSummary function to access the same group based sub total.

              All the records in a FoundSet

              If you put that summary field in a layout part other than the sub summary part, you get the total for all the records in the current found set.

              If you refer to a summary field in a calculation field defined in the same table as the summary field, it will also return a total for the current found set. (Which is why we have the GetSummary function to get sub totals in calculations.)

              Summary field is referenced on a layout based on a table related to the table in which it was defined:

              Not in a Filtered Portal

              If you place the summary field on a layout based on a related table or refer to it in a calculation defined in a related table, the relationship controls the value that is computed. It will be based on all the records in the summary field's table that are related to the current record in this table.

              Think of it this way, if you put a portal on this layout to the summary field's table, you'd see all the records in this portal that are used to compute the summary field's value in this context.

              In a Filtered Portal (FileMaker 11 and newer only)

              If you place that summary field inside a portal with a filter, you no longer get a value based on all the related records. Instead, you see a value based on all related records for which the filter expression evaluates as True.

              This is a special case use of a summary field that is often implemented by putting a single row copy of a filtered portal below it with the summary field inside so that the user sees a value based on just the records visible in the larger portal.

              This is a "Display Only" trick as you cannot refer to the value of this field in a calculation and get the same value shown on the layout--you get the result described in "Not in a Filtered Portal" above.

              Note that this does not just apply to "total" summary fields, Average, Count, Maximum, standard deviation, etc all follow these same rules.