1 2 Previous Next 17 Replies Latest reply on Sep 30, 2014 7:03 AM by camcorp

    doing maths with SQL and Filemaker?

    camcorp

      Hi

      I'm New usig SQL in FM

      I succesfully made a query that retrieve grouped Data from a large database.

      for the last 12 months I summarized information of two field.

      I used the following SQL calculation

       

      ExecuteSQL(

      "SELECT TimePeriod , SUM (Total_Sales) AS totalsales , TimePeriod , SUM (Total_Margin) AS totalmargin

      FROM bbdd

      WHERE bbdd.id_TimePeriod >= ? AND bbdd.Branch = ?

      GROUP by TimePeriod";

      ""; ¶; report::id_last_12M; report::branch)

       

      the field display the data in this way for every branch

      TimePeriod Total_Sales TimePeriod Margin

      2013M09457952013M09199
      2013M10539872013M10769
      2013M11532172013M11862
      2013M12523442013M12691
      2014M01515212014M01643
      2014M02503252014M02625
      2014M03496982014M03714
      2014M041810432014M043083
      2014M051958012014M052172
      2014M061935162014M061855
      2014M071915722014M072656
      2014M081886522014M082752

       

       

      Right Now I can use the data for charting purposes, and work great and is very flexible.

      I want to have more information and I'm trying to do the following calculation, the margin as a percentage of total Sales for each group of data base on the timeperiod line.

      I triyed to do a new column like the following one

      SELECT TimePeriod , SUM (Total_Sales) AS totalsales , TimePeriod , SUM (Total_Margin) AS totalmargin , TimePeriod, totalmargin / totalsales AS percentmargin

       

      but it din't worked.

      The question is what did I missed?

      FM can't do the work I'm looking for?

      I don´t like using scripts to fill data, that's why I found until now SQL great.

      Kind regards.

      camcorp

        • 1. Re: doing maths with SQL and Filemaker?
          user19752

          This is not documented but testing result shows that FM SQL is not support

          refering column alias in column list (like as you do)

          calculate using aggrigate function (like SUM(field1)/SUM(field2))

           

          add:

          workaround

          1) use custom function

          2) evaluating technique if your result is short enough (about 30,000 chars)

          Let ( [

          sql ="SELECT n,n+1,'<<<' || n || '/' || (n+1) || '>>>' FROM table WHERE n<12" ;

          res = ExecuteSQL ( sql ; "" ; "" )

          ];

          Evaluate ( Substitute ( Quote ( res ) ; [ "<<<" ; "\"&(" ] ; [ ">>>" ; ")&\""] ) )

          )

          • 2. Re: doing maths with SQL and Filemaker?
            wimdecorte

            camcorp wrote:

             

            I don´t like using scripts to fill data, that's why I found until now SQL great.

             

             

            Why don't you like scripts?

             

            While SQL is a great tool, it is not always going to be faster than more traditional scripted approaches.  In fact, sometimes it is going to be massively slower, especially if the complexity of the query goes up and the amount of math in the query increases (or any other operators like MAX(), ...)

             

            So a little word of caution here, by discarding regular scripting and using calculated fields with SQL queries, your solution can become very sub-optimal from a performance point of view.  And from a maintenance point of view too.  Scripts are easy to maintain, database schema not so much.

            • 3. Re: doing maths with SQL and Filemaker?
              camcorp

              Hi thanks!!

              But I can't make it work the SQL syntax

              regards

              CAMCORP

              • 4. Re: doing maths with SQL and Filemaker?
                user19752

                On your case

                 

                sql="SELECT TimePeriod , SUM (Total_Sales) , TimePeriod , SUM (Total_Margin) , TimePeriod, '<<<' || SUM (Total_Margin) || '/' || SUM (Total_Sales) || '>>>'

                FROM

                ...

                 

                It may be better using ROUND and *100 for percentage.

                 

                It seems your result will never contain < and > chars, so the <<< and >>> can be < and >.

                • 5. Re: doing maths with SQL and Filemaker?

                  Theres a 3rd possability to do maths on aggregates:

                   

                  3. VirtualList technic to do calculations on sql-aggregates.

                   

                  Assuming the table VirtualList is filled with the global variable $$vl, the colomn names of the VL are Field01...FieldXX and the virtuallist column separator is "||", you could do it like this.

                   

                  Let ( [

                   

                  $$vl = ExecuteSQL (

                  "SELECT TimePeriod , SUM (Total_Sales) AS totalsales, SUM (Total_Margin) AS totalmargin

                  FROM bbdd

                  GROUP by TimePeriod"

                  ; "||" ; ""; report::id_last_12M; report::branch )

                   

                  ] ;

                   

                  ExecuteSQL (

                  "SELECT Field01, Field02, Field01, Field03, Field01, NUMVAL(Field03) / NUMVAL(Field02)

                  FROM VirtualList

                  WHERE Field01 IS NOT NULL"

                  ; "" ; "" )

                   

                  )

                   

                  the NUMVAL operator is needed, if the virtuallists fields are text fields.

                  This all can be done in just one calculation step.

                   

                  Regards Otmar

                  • 6. Re: doing maths with SQL and Filemaker?
                    camcorp

                    Hi thanks again, but I can't make it work , the sql  query works great up to '<<<' if I write more I get ? error sign.

                    regards

                    camcorp

                    • 7. Re: doing maths with SQL and Filemaker?
                      camcorp

                      Hi Otmar

                      thanks for your answer

                      I did as you suggested but the only result is the following

                      2013M09||45795||199

                           :

                            :

                      2014M08||188652||2752

                       

                      The second part of the calculation does not affect the result

                       

                      best regards

                      camcorp

                      • 8. Re: doing maths with SQL and Filemaker?
                        camcorp

                        Hi wimdecorte

                        you are right with your suggestion, but I'm doing this only for report purposes, so speed its not an issue.

                        The main objective is to replace excel to provide a nice report that is easy to mantain and to deliver

                        best regards

                        camcorp

                        • 9. Re: doing maths with SQL and Filemaker?

                          Hi Camcorp

                           

                          You have to implement the VirtualList technic first. Search for "FileMaker VirtualList" or read this: http://www.mightydata.com/blog/virtual-list-in-three-easy-steps/

                          The VirtualList is a table, where its records get populated by a $$ variable.

                           

                          best regards

                          Otmar

                          • 10. Re: doing maths with SQL and Filemaker?
                            user19752

                            Ouch! very sorry, I was a bonehead... using aggregation in calculation

                             

                            This is working in my table, can you apply it ?

                             

                            Let (

                            result = ExecuteSQL ( "

                            SELECT SUM(id), SUM(id+1), '<<<', SUM(id), '===', SUM(id+1), '>>>'

                            FROM testTable

                            GROUP BY text

                            " ; "" ; "" )

                            ;

                            Evaluate ( Substitute ( Quote ( result ) ; [ "<<<," ; "\"&Round(" ] ; [ ",===," ; "/" ] ; [ ",>>>" ; "*100;2)&\"" ] ) )

                            )

                            • 11. Re: doing maths with SQL and Filemaker?
                              user19752

                              If use a table to store the result, 'Import records from ODBC' and make a calculation field may be simple.

                              • 12. Re: doing maths with SQL and Filemaker?
                                camcorp

                                I think I'm the bonehead guy

                                I did the following

                                 

                                 

                                Let (

                                result = ExecuteSQL ( "

                                SELECT SUM(Total_margin), SUM(Total_sales), '<<<', SUM(Total_margin), '===', SUM(Total_sales)), '>>>'

                                FROM bbdd

                                WHERE bbdd.TimePeriod    >=    ? AND bbdd.branch = ?

                                GROUP BY TimePeriod

                                " ; "" ; "" ;report::id_last_12M; report::branch)

                                ;

                                Evaluate ( Substitute ( Quote ( result ) ; [ "<<<," ; "\"&Round(" ] ; [ ",===," ; "/" ] ; [ ",>>>" ; "*100;2)&\"" ] ) )

                                )

                                 

                                I undestand the formula but I can't get the error

                                 

                                I'm exploring a fourth aproach with a recursive calculation

                                regards

                                camcorp

                                • 13. Re: doing maths with SQL and Filemaker?
                                  user19752

                                  You have too many closing ) here.

                                   

                                  SELECT SUM(Total_margin), SUM(Total_sales), '<<<', SUM(Total_margin), '===', SUM(Total_sales)), '>>>'

                                   

                                  If you have FMP advanced, watch the formula in data viewer as

                                  Let ( r = ExecuteSQL ( ... ) ; If ( r <> "?" ; r ) )

                                   

                                  Sometimes this help us, but sometimes only show a message "There is an error in the syntax of the query."

                                  • 14. Re: doing maths with SQL and Filemaker?

                                    The VirtiualList is not a table where a result get stored, it only reflects the result, abit like a temp table in memory. Once set up, it is a great construct for reporting.

                                     

                                    have a look at the attached demo.

                                    1 2 Previous Next