9 Replies Latest reply on Dec 4, 2014 3:54 AM by coherentkris

    Distinct + Sum in ExecuteSQL

    renti

      Hello,

       

      I´m trying to calculate the total value of the items in my stock using Execute SQL with the sentence underneath, but filemaker doesnt recognize it.

       

      ExecuteSQL

      ("

      SELECT DISTINCT Item_ID , SUM (stock_value)

      FROM T03_Warehouse_movements

      where Date_field < ?

      " ; "" ; "" ; g_date)

       

      i only can make it working without the SUM, but then formatting the text is a nightmare.

       

       

      thanks,

        • 1. Re: Distinct + Sum in ExecuteSQL
          coherentkris

          Is ExecuteSQL returning a "?" on an unexpected result?

          • 2. Re: Distinct + Sum in ExecuteSQL
            ahusid

            Maybe you have to include an "GROUP BY Item_ID" statement.

             

            The  SUM, Count, Max, Min, Average, and other sumarize functions, works in conjunction to the Group By Statement.

            • 3. Re: Distinct + Sum in ExecuteSQL
              renti

              Thanks,

               

              yes including GROUP BY it works. now the problem is formatting the text. How could i just retrieve the values between the "," and the ¶ ?

               

              1,2592¶

              8,0¶

              12,0¶

              30,216.645¶

              • 4. Re: Distinct + Sum in ExecuteSQL
                coherentkris

                if ExecuteSQL returns a list =

                1,2592¶8,0¶12,0¶30,216.645¶

                Then

                GetValue ( Substitute ( GetValue ( list ; 3 ) ; "," ; "¶" ) ; 1 ) returns 12

                or

                GetValue ( Substitute ( GetValue ( list ; 1 ) ; "," ; "¶" ) ; 2 ) returns 2592

                • 5. Re: Distinct + Sum in ExecuteSQL
                  renti

                  the problem is that i dont know how many items in the list it´s going to be. it seems a tail function is required to format the list.

                  i´ve found this link: http://www.seedcode.com/sql-subqueries-in-filemaker/

                   

                  where the author post a similar case to mine and work it out just with one ExecuteSQL function, but something´s wrong on my side:

                   

                  ExecuteSQL

                  ("

                  SELECT SUM (stock_value)

                  FROM T03_Warehouse_movements

                  WHERE Item_ID  IN (SELECT DISTINCT Item_ID  FROM T03_Warehouse_movements) and Date_field < ?

                  " ; "" ; "" ; g_date)

                   

                  i got the sum for all the values of the table that match the date_field filter, but the DISTINCT Item_ID doesnt seem to apply......

                  • 6. Re: Distinct + Sum in ExecuteSQL
                    coherentkris

                    Suppose....Table T03_Warehouse_movements

                    Field

                    item ID stock value Date_field

                    1          3               1-2-2014

                    1          3               1-2-2014

                    2          4               2-1-2013

                    2          56               12-25-2012

                    5          0               5-2-2000

                    4          34               8-4-2016

                    and executing the sub query only ...SELECT DISTINCT Item_ID  FROM T03_Warehouse_movements

                    Will return 1,2,4,5

                    Then All records in T03_Warehouse_movements will be returned by your WHERE IN of the parent query

                    Basically the where in does nothing and the only discriminator is the date criteria

                    • 7. Re: Distinct + Sum in ExecuteSQL
                      beverly

                      1.

                      Check out this method of pushing the SQL results to a table:

                           "Lesson 39: ExecuteSQL and Virtual Lists"

                      In the FileMaker Training Series, Advanced.

                      <http://www.filemaker.com/support/training/fts.html>

                       

                      If you are a subscriber TechNet member the FTS is an included benefit.

                           "FileMaker Developer Subscription"

                      <http://www.filemaker.com/technet/>

                       

                      2. another method is to make the results an HTML table and view in Web Viewer

                       

                      3. It depends on what you mean by "formatting". There are other ways to clean up the results as viewed in the field.

                       

                      I suspect you meant pulling the data from the results.

                       

                      Beverly

                      • 8. Re: Distinct + Sum in ExecuteSQL
                        renti

                        Stock_value is the same for each item_ID ( item_ID 2 in your table should then be = 4 or 56)

                         

                        For your above example using DISTINCT or not

                         

                        ExecuteSQL

                        ("

                        SELECT DISTINCT Articulo_ID 

                        FROM T03_Almacen_DETALLES_ALMACEN

                        " ; "" ; "" )

                         

                        --> list of 4 values

                         

                        and same sentence without DISTINCT

                        --> list of 6 values

                         

                        I dont understand why including SUM (value_stock) to the Select changes the list of values for the SUM to 6....

                         

                        How could i get then the SUM for the stock_value for each distinct item_ID ? (3 + 4 + 0 + 34) in your table using ExecuteSQL ?

                        • 9. Re: Distinct + Sum in ExecuteSQL
                          coherentkris

                          Try

                          Execuite SQL ( "SELECT SUM ( stock_value ) FROM T03_Warehouse_movements WHERE Date_field = ? GROUP BY Item_ID" ; "" ; "" ; g_date )