Members

10

0 Neutral

• Rank
member

• Gender
Not Telling
1. ## How to average a subquery

I'm drawing on a database of 100k+ records. First Query - retrieves up to 1,000 results (each is a SUM of up to 1-1,000 records - GROUPS of records) SELECT SUM(t) as sumT FROM Briefs WHERE t>4 AND Group='ABC' AND Status ='1' GROUP BY myID ORDER BY sumT ASC Grouped Data = (1,2,3) (10,2,3,5) (23,2) (10,90) >> SUMS = 6, 20, 25, 100 (could be up to a 1,000 results) Next I calculate quartile info - Q1, Q3, IQR, Upper/Lower limits (\$Upper might be 45) Second Query (orig post) - similar to First Query but limiting the SUMS by the HAVING clause (45) using UpperLimit from quartile calculations) SELECT SUM(t) as sumT FROM Briefs WHERE t>4 AND Group='ABC' AND Status ='1' GROUP BY myID HAVING sumT>\$Upper ORDER BY sumT ASC >> SUMS = 6, 20, 25, 100 Now I calculate the adjusted AVERAGE (= 17) of the Second Query resulting SUMS = 6, 20, 25 (with custom function) >> AVG = 17 Steps 2/3/4 could be combined into a custom function (is there a simple function that returns a list from a list based on a maximum value - any ideas?) i.e. loop through sorted SUMS (step 1), total SUMS up to \$Upper (45 - step 2), then average the total (step 4) Just thought it might be faster to ExecuteSQL >>> AVG( Step 3 query ) - to get the single number output. The above sequence to be performed approx 3,640 times to produce various graphs for analysis (each week). I will also try to produce an SQL using clauses and subqueries to simplify: e.g. from Step 1 >> SUMS = ABC | 1 | 6, 20, 25, 100 >> SUMS = ABC | 2 | 16, 20, 25, 33 >> SUMS = XYZ | 1 | 20, 20, 25 >> SUMS = XYZ | 2 | 6, 20, 25, 100, 200 >> SUMS = QJZ | 1 | 1, 11, 22, 33, 55, 77, 200, ... 13600 >> SUMS = QJZ | 2 | 11, 88 Hope this give a better picture to what i'm trying to accomplish. Any help/suggestions welcome
2. ## How to average a subquery

I currently have a custom function doing this, but it seems a fiddly workaround and inefficient for this purpose as eventually I'll be iteratively calling this SQL (or write a more complex call if I can work it out).
3. ## How to average a subquery

Unfortunately not. I need the average of the SUMs. e.g. 1. the AVG of the SUMs (100,120,123,123,290) = 756 / 5 = 151.2 2. the AVG of the diffDateTime could be ((10,20,30,40), (10,20,20,30,40), (10,20,30,40,23), (10,20,30,63), (10,20,30,40,50,50,90), = 756 / 25 = 30.24
4. ## How to average a subquery

Hi, i am a novice when it comes to anything more than basic SQL and am stuck trying to get an average (using SQL) of grouped query result. My first query is: SELECT SUM(diffDateTime) as theDiffSum FROM Briefs WHERE diffDateTime>4 AND Group1 = 'ABC' AND Status = '1' GROUP BY ows_ID HAVING theDiffSum>99 ORDER BY theDiffSum ASC this gives me a result like: 100 120 123 123 290 Ideally, i would like to form a query on this result set to give an average - a single number, using SQL (not FMP functions). I've looked around but have been unable to find anything that works (or understand). e.g. SELECT AVG( SELECT SUM(diffDateTime) as theDiffSum FROM Briefs WHERE diffDateTime>4 AND Group1 = 'ABC' AND Status = '1' GROUP BY ows_ID HAVING theDiffSum>99 ORDER BY theDiffSum ASC) as test Any ideas would be greatly appreciated - it is really screwing with my brain (or what's left of it?
5. ## Calculation field not using updated Custom Function

Thanks so much - you guys are fantastic!! Problem is now solved. However, i feel embarrassed that I overlooked the "Do Not store" attribute - I have been using Filemaker since version 1 (before Filemaker Plus even!). I am new to using CFs, they work very well. thanks again Wayne
6. ## Calculation field not using updated Custom Function

Hi, i'm not sure if this is the normal practice for Custom Functions. I have made a change to my CF and expected the change to be reflected in the calculation fields that use the CF. However, all previously defined calculation fields remain stuck to the "older" version of the CF, although the CF may have been radically changed. To force the newly updated CF to be used, I have to edit the calculation field (in define fields) and make a change to force a recalculation. This seems to be wrong behaviour, it is as if the calculation field copies the CF available at time of field definition/creation (or changes to it). Is there some way to force a recalculation to reflect the changed CF, either automatically or scripted? Or could there be something screwed up in my settings or a version bug? (using FMP 13.02 Advance) thanks for any advice Wayne
7. ## How to do a calculation on an aggregate function in an ExecuteSQL statement

I tried a*t as test, as suggested Fitch, but no luck. Maybe calculations on aggregates just not work with filemaker SQL at this stage, as eluded to by Electric_Soul. Perhaps I'll have to load up MAMP (does it still exist??) and play with mySQL, etc.
8. ## How to do a calculation on an aggregate function in an ExecuteSQL statement

Hi, I have very limited SQL experience and am trying to do some calculations on a SELECT statement in a local FMP database; When I do: SELECT COUNT(tab) as t, SUM("amounts") as a FROM SummaryData WHERE tab >15 GROUP BY tab Result is: 16, -152 17, 113 18, 255 When I add in a calculation: SELECT COUNT(tab) as t, SUM("amounts") as a, a * t FROM SummaryData WHERE tab >15 GROUP BY tab Result is (invalid): ? I have tried various combinations that seem to work on standard SQL systems, but Filemaker errors. Does Filemaker SQL not support calculations? Any ideas or pointers would be greatly appreciated.
9. ## Container field not updating

I didn't plan on that. Makes a whole lot of sense. I will update the "parent" table (1 record) to use standard fields and test it out. I am sure that will solve it. Many thanks !
10. ## fmgo 13 Container field not updating

Hi, this might be something i am not doing right, however.... When i place a new picture (png) into a global container field in my mac FMP13 solution, the field is updated throughout the solution. But, when i then copy the FMP13 file to iPhone to run with FMGO 13, the container field retains the old picture (like it is cached somehow). I have tried flushing cache steps and refreshing object, but this not seem to fix it. The only way to force an update (for the mobile to display properly) is to do a new compressed version or create a new kiosk-version. This is seriously doing my head in Is there some script step or defined way to force the updating of the container fields so when it appears on the mobile device, the field displays the correct image? Thanks in advance. Wayne
×
×
• Create New...