AnsweredAssumed Answered

Math with Aggregates in ExecuteSQL

Question asked by kmtenor on Jul 6, 2012
Latest reply on Jul 11, 2012 by 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

Outcomes