6 Replies Latest reply on Nov 26, 2014 5:26 PM by renti

    Group by - ExecuteSQL

    renti

      Hello,

       

      I´ve a problem with the following sentence:

       

      ExecuteSQL ("

      Select SUM (Hours_employee) , Unique_mark

      From T02_Partes_EMPLOYEES

      Where date = ? AND Id_employee = ?

      Group by Unique_mark

      " ; "" ; "" ; date ; Id_employee)

       

       

      I got the right result, but repeated for each row of the Group. I´d like to got just ONE result row for each group as i read it in the ExecuteSQL manual.

       

      regards,

        • 1. Re: Group by - ExecuteSQL
          mikebeargie

          Have you tried:

           

          ExecuteSQL ("

          Select SUM (Hours_employee) , Unique_mark

          From T02_Partes_EMPLOYEES

          Where date = ? AND Id_employee = ?

          Group by Unique_mark

          Order by Unique_mark ASC

          " ; "" ; "" ; date ; Id_employee)

           

          I seem to recall that groupings require an ordering in FM as well by the same field in order for it to work.

           

          I seem to also recall that using group by in ExecuteSQL can be slower than subsummarizing a report layout in some cases, use it with caution.

          • 2. Re: Group by - ExecuteSQL
            erolst

            I'm rather surprised you did get a result at all; 'date' is a reserved word in SQL (in general, it is not a good idea to use a data type as a field name; and by itself, it isn't that meaningful),

             

            Besides that, your code should work; see attached.

            Mike Beargie wrote:

            I seem to recall that groupings require an ordering in FM as well by the same field in order for it to work.

            I think you can add ORDER on top of GROUP; but it seems to work without.

            • 3. Re: Group by - ExecuteSQL
              renti

              hi,

               

              about the issue with date as fieldname, i changed it in the post, to make it easier to understand, because my project is in spanish.

               

              i´ve added Order by underneath Group by (above the SQL sentence doesnt work) and it makes no difference.

               

              i got the right result as you can see in the screenshot attached, but i got the result repeated (in my case 137) for each row of the group -i wish to have just one for each group-, but maybe it missunderstood the manual

              Sin título.jpg

              • 4. Re: Group by - ExecuteSQL
                beverly

                It *will* be repeated because you are making the same query in EACH row/record. When there is more than one "match" those records will get the  same result.

                 

                You are also showing a "found set", and trying to combine SQL within a sorted / summarized list.

                 

                Do you have an example of what you expect it to look like?

                 

                Beverly

                • 5. Re: Group by - ExecuteSQL
                  erolst

                  renti wrote:

                  i got the right result as you can see in the screenshot attached

                   

                  Any special reason to use SQL in the first place? You can simply try adding a sub-summary part by Unico and placing a summary field “Total of” into it (and, of course, sort the list). This is how you generate groups in FileMaker, and show summary values only once per group.

                   

                  SQL is often used for this purpose if you want to operate without a FileMaker list layout; since you are already using such a layout, there are many “native” tools at your disposal to generate and display such aggregate values.

                  • 6. Re: Group by - ExecuteSQL
                    renti

                    Hi Beverly,

                     

                    Yes, i was making the same query in each row. I just deleted the useless one with an script and it seems to work now.

                     

                    thanks for your support