4 Replies Latest reply on Jul 11, 2012 2:46 PM by kmtenor

    Math with Aggregates in ExecuteSQL

    kmtenor

      I have a need to dynamically create a list of percentages based on a total query. Our business is a sawmill, and we want to report the percentage of volume produced by grade - and the grades produced on a particular run are variable.

       

      Building the database in Filemaker 12, I figured this data would be easy to get at using ExecuteSQL. However, I'm finding that ExecuteSQL (and I'm guessing Filemaker SQL in general) will not allow math with aggregates. For instance within a table called "history_production_run", which includes the grades and the volumes produced for those grades (across multiple individual products), the following query works:

       

      select

      prod_grade,

      sum(prod_volume) as gradeVol,

      (select sum(prod_volume) from history_production_run where prod_id = 'c-p-8lr-062612') as totalVol

      from history_production_run

      where prod_id = 'c-p-8lr-062612'

      group by prod_grade

       

      A portion of the output looks like this (obviously, there's more...):

       

      3BTR912172173
      CSEL813172173
      CTRM2536172173
      DBTR500172173
      DFR9596172173

       

      Tantalizingly close, since the data I need for the calculation is RIGHT THERE! However, when I modify the query to attempt to divide the "gradeVol" calculation by the "totalVol" number (in order to produce the percentage), I get the dreaded "?" in my query calculation field:

       

      select

      prod_grade,

      sum(prod_volume) as gradeVol,

      (select sum(prod_volume) from history_production_run where prod_id = 'c-p-8lr-062612') as totalVol,

      (sum(prod_volume) / (select sum(prod_volume) from history_production_run where prod_id = 'c-p-8lr-062612')) * 100 as gradePct

      from history_production_run

      where prod_id = 'c-p-8lr-062612'

      group by prod_grade

       

      I have attempted the query which produces the percentages in the original database (which is MSSQL, so the fields and tables were modified accordingly), and it works just fine - I can use aggregates in the calculation, and the group by clause can remain as above, and I get results.

       

      I also attempted the second query in a JDBC interface called "SquirrelSQL", so I could see what (if any) error was being returned by the FMS Engine, and it returned the error: "Expressions involving aggregations are not supported."

       

      My questions are:

       

      1. Is it somehow possible to perform this query another way with Filemaker SQL? If so, I'm missing it.

      2. If not ExecuteSQL, what other method would folks use to get at data this way? I've been developing in Filemaker for years, and dynamic calculations like this have always been a frustration. I'm using a Pie Chart to get at this data right now, but there are so many grades on each run that when the percentages are displayed on the chart, the labels all run into one another and the text can't be read. I need to show it in tabular form in order for it to be useful for my end-users.

       

      Thanks in advance for any assistance.

       

      -Kevin

        • 1. Re: Math with Aggregates in ExecuteSQL
          greglane

          Hi Kevin,

           

          Try using DISTINCT instead of GROUP BY. Then you can use a correlated subquery to sum prod_volume for each unique prod_grade. Functions and math operators can be used on the results of the subqueries. This should be pretty close. Let me know if it doesn't make sense.

           

          SELECT distinct prod_grade,

                 (SELECT Sum(prod_volume) AS gradeVol

                  FROM   history_production_run

                  WHERE  history_production_run.prod_grade = hpr.prod_grade),

                 (SELECT Sum(prod_volume)

                  FROM   history_production_run

                  WHERE  prod_id = 'c-p-8lr-062612') AS totalVol,

                 Round((SELECT Sum(prod_volume) AS gradeVol

                        FROM   history_production_run

                        WHERE  history_production_run.prod_grade = hpr.prod_grade) /

                             (SELECT Sum(prod_volume)

                              FROM history_production_run

                             WHERE prod_id = 'c-p-8lr-062612') * 100, 2) AS gradePct

          FROM   history_production_run hpr

          WHERE  prod_id = 'c-p-8lr-062612'

           

          Greg

          1 of 1 people found this helpful
          • 2. Re: Math with Aggregates in ExecuteSQL
            kmtenor

            WOW!

             

            Thanks, Greg.  That makes sense, though it's definitely the LONG way around the horn!

             

            There were a couple of constraints you left out, but once I added those back in, it seems to have worked.  Unfortunately, all of the sub-selects seem to be slowing things down quite dramatically in comparison to the other query.  I'll work with it a bit and see if I can get it smoothed out, though.

             

            If I'm not mistaken, then, the most important thing you said in your post is that "Functions and math operators can be used on the results of the subqueries".  In other words, we can't do math using aggregates (because of limitations in the FMSQL engine), but we can do math and other functions on subqueries that are generating aggregates as their only output.

             

            Thanks again.

             

            -Kevin

            • 3. Re: Math with Aggregates in ExecuteSQL
              greglane

              Glad it worked Kevin. There's definitely room for some optimization. One quick change you could make would be to pass the total to the query as a parameter. This example only calculates the totalVol one time, which should help significantly.

               

              ExecuteSQL("

              SELECT distinct prod_grade,

                     Round((SELECT Sum(prod_volume) AS gradeVol

                            FROM   history_production_run

                            WHERE  history_production_run.prod_grade = hpr.prod_grade) /

                                 ? * 100, 2) AS gradePct

              FROM   history_production_run hpr

              WHERE  prod_id = 'c-p-8lr-062612'

              "; ""; ""; ExecuteSQL("SELECT Sum(prod_volume)

                                  FROM history_production_run

                                 WHERE prod_id = 'c-p-8lr-062612'";"";"")

              )

               

              BTW, it might be "cleaner" to use a Let function so the two ExecuteSQL functions aren't nested. That would also give you a chance to wrap the main ExecuteSQL function in a Case or If to test for totalVol = 0 and avoid the dreaded "?" from a divide-by-zero error.

               

              Hope that helps.

               

              Greg

              1 of 1 people found this helpful
              • 4. Re: Math with Aggregates in ExecuteSQL
                kmtenor

                Excellent, Greg.

                 

                Thanks again for diving in here.

                 

                The final calculation is pasted below, so that others can share in the fun.  This version definitely runs faster, and is a lot more readable than the first version with all the correlated subqueries.  The trick was remembering to get the prod_id constraint in all the right places so that it would a)work and b)be accurate.

                 

                As much fun as this has been, I have found another road block for ExecuteSQL in this project.  At the risk of hijacking my own thread, I'll just say that I'm having trouble getting the "where <field> in <list>" to work when <list> is passed as a parameter.  I've done some experiments, and will post a new thread to get THAT figured out!

                 

                Thanks again for your help.  Here's the code:

                 

                Let (

                $run_volume = ExecuteSQL ("select sum(prod_volume) from history_production_run where prod_id = ? and prod_category='Produced'";"";"";hist_run_id);

                 

                ExecuteSQL("

                SELECT

                distinct hpr.prod_grade,

                  round((SELECT sum(hp.prod_volume) as gradeVol

                    FROM history_production_run hp

                    WHERE hp.prod_grade = hpr.prod_grade and

                    hp.prod_id = ?

                    GROUP BY hp.prod_grade)/?*100,2)  AS gradePct

                FROM history_production_run hpr

                WHERE hpr.prod_id = ? and

                hpr.prod_category = 'Produced'

                "

                ;Char ( 9);"";hist_run_id;$run_volume;hist_run_id)

                )

                 

                -Kevin