Sign in to follow this  
Followers 0
madmac33

How to do a calculation on an aggregate function in an ExecuteSQL statement

7 posts in this topic

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.
 
 

Share this post


Link to post
Share on other sites

Lets say that Filemakers SQL power is kinda limited. The more you dive in, the more problems you'll encounter. Filemaker is using SQL since Version 10 I think. You have to give them some time :)

Share this post


Link to post
Share on other sites

Maybe a*t as something? Don't have time to test right now. But I think it is possible.

 

http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

 

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.

Share this post


Link to post
Share on other sites

When I think about it.......     your query must not work. On which database did your sucsseslfully try your query, where you referenced the aliases in the select  statement?

 

There is a processing order in sql. And this order evaluates aliases with the select statement. The selection of rows comes before that.

 

A simple query like this should not work either, because the where clause doesn't know w. w is being evaluated wit the select clause, and that one comes after there where clause.

 

select weight as w,FROM garbage
where w > 10
 

Share this post


Link to post
Share on other sites

It has been my experience that filemaker FQL does not like it when you mix aggregate functions with non aggregate functions. If you have Filemaker Advanced you could write a recursive custom function that processes your SQL array result to arrive at the desired result.

Share this post


Link to post
Share on other sites

It has been my experience that filemaker FQL does not like it when you mix aggregate functions with non aggregate functions. If you have Filemaker Advanced you could write a recursive custom function that processes your SQL array result to arrive at the desired result.

Instead of a CF you could use the VirtualList technic to do calculations on sql-aggregates.
 
Assuming the table VirtualList is filled with the global variable $$vl and the virtuallist column separator is "||", you could do it like this.
 
Let ( [
 
$$vl = ExecuteSQL ( 
"SELECT COUNT(tab), SUM("amounts") 
FROM SummaryData 
WHERE tab >15 GROUP BY tab"
; "||" ; "" )
 
] ;
 
ExecuteSQL ( 
"SELECT Field01, Field02, NUMVAL(Field01) * NUMVAL(Field02)
FROM VirtualList
WHERE Field01 IS NOT NULL"
; "" ; "" )
 
)
 
the NUMVAL operator is needed, if the virtuallists fields are text fields.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • ExecuteSQL and Running Totals
      By Sam Reicht
      Hi,
      I need help with SQL and Running Total (instead of Total). 
       
      TABLES
      INV = Invoice table
      - INV::Amount     ( Amount of Invoice) - INV::DateMonth ( Month of InvoiceDate) - INV::DateYear    ( Year of InvoiceDate)   DTE = Months table (fixed 12 records) - DTE:Order=1...12) - DTE::CurYear = 2016     Below SQL request works, see my result! But what I would require is a running total, adding each month sales total, see my "Required result"   ExecuteSQL ( "
      SELECT      SUM(a.\"Amount\"/1000)
      FROM          \"DTE\" b
      LEFT JOIN   \"INV\" a
      ON                 b.\"Order\"     = a.\"DateMonth\"
      AND               b.\"CurYear\" = a.\"DateYear\"

      GROUP BY    b.\"Order\"
      "; " | " ; "0¶"  )

        My current result as above SQL request
      726.605050
      818.7150
      739.067850
      0
      703.7210
      620.805350
      599.986160
      506.9230
      597.8370
      801.78250
      1014.11510
      567.64575  
      Below is what I require (a Running Total)
      726.60505
      1545.32005
      2284.3879
      2284.3879
      2988.1089
      3608.91425
      4208.90041
      4715.82341
      5313.66041
      6115.44291
      7129.55801
      7697.20376
       
      I can do this without SQL, but I want to do using SQL and use it for my Charts.
      Would appreciate any help. My SQL understanding is basic and I am using FileMaker 14 Adv
    • Seeking Pointer: New to FM, Oracle/SQL background
      By kisertn
      I am new to FM, coming to it from a SQL Server/Oracle/Access background.  I have created a database with layouts and while there is certainly much more for me to learn it generally is working as I desire it.  However, I need to perform a task that I do not understand how it would be done in FM.  The example below is not my actual problem but does exemplify my core issue and hopefully is easier to understand than if I described my project.
      I have a table of Users, a table of Entitlements, and a join table of UserEntitlements.    I want to execute SQL that, for a given User, will read from the Entitlements table and insert rows into UserEntitlements for this user.  The query will read from Entitlements, insert a row for every entitlement for this user and, based on data in Entitlements take, will mark some of the entitlements as 'active'.
      Ideally, every time I created a new user this SQL would be called  (I'd have it as a trigger in a SQL or Oracle database, or I'd have a batch process that ran frequently looking for new users).   Basically, as a new user is created I want to create their entitlements profile and activate standard entitlements.
      I don't need/expect a full answer - was just hoping I could get pointed in the right direction: is 'scripts' the area I need to explore?  Is there a SQL capability to FM?  I have not found it if there is.
      Thank you for any assistance you can provide,
      -Neil
       
    • ExecuteSQL use with Virtual Lists
      By Stu412
      Hi all
       
      Hopefully this is in the right place, apologies if not.
       
      I'm using the technique found in the FM 13 advanced training series to generate virtual lists.
       
      I'm using virtual lists to quickly transfer data to a scratch table used for reporting.  I have an ExecuteSQL formula to gather some of the data in question:
      ExecuteSQL (
      "
      SELECT GroupID, SUM(RoundedValue)
      FROM DataTable_Home
      WHERE  CustID_FK = ? and Code < ? and PeriodNumber = 2 and
      (GroupID = 68 or GroupID = 3)
      GROUP BY GroupID
      ";
      "|" ; "|" ;
      Cust_Home::CustID_PK;
      "500"
      )
       
      This generates in most cases a dataset of:
      68|-3600|3|-7200| <<< as an example for this customer with 68 and 3 being the required group 'names' or headers.
       
      This data is passed via scripting into a field on the scratch table called ListData which has several fieldsdedicated to particular values, two of which are dedicated to Group68 values and Group3 values.  The formula in each is:
       
      Get Value(ScratchTable::ListData;2)<<<For Group 68, -3600
      Get Value(ScratchTable::ListData;4)<<<For Group 3. -7200
       
      However, there may be cases where a customer does not have any values associated to either 68 or 3 which obviously reduces the return on the dataset and makes the parse section inaccurate.  On my scratch table, I have several fields
       
      If a particular customer has NO value for group 68, the returned data set instead looks like:
      3|-7200
      and this will break all downstream GetValue(Field;x) calculations.
       
      So, what I really need to have is a reliable way of ALWAYS returning 4 pipe separated results in the situation that a customer has data missing from one or other of the groups, and that's where I'm stuck.  If I could get to:
       
      68|0|3|-7200 or
      68|-3600|3|0
      as an example, this would enable me to parse the 0 values to where they should be.
       
      Any help on this one greatly appreciated.
       
      Thanks
    • Help with Select Sum
      By hair on fire
      Hi all,
      I'm relatively new to the ExecuteSQL statement but I want to learn more about it in order to create a dashboard of charts without having to worry about the underlying layout's base table.  I am a lab manager who oversees 4 funds each with different fiscal years.  I'd like to see real time totals for each fund.   I'm stuck with how to incorporate the various fiscal years to the total spend for each fund.  Do I need a statement for each fund?
      I have tables for Orders, Funds and a Dashboard.
      I was able to successfully write an SQL statement that allows  me to chart how much each lab user is spending each calendar year (via a User table).  But this has me stumped!
      Let me know what information would be helpful for those out there who might help me!
      Many thanks in advance!
       
       
    • SQL novice - where to start?
      By Richmilnix
      A simple project wants to start with several people out in the field doing data entry; in the interest of uniformity, I set up a simple one-table web-based SQL table with a one-way PHP interface. That's working fine (and is about the limit of my facility with PHP). My intent was to set up an automated recurring import into a FMP database, which I thought would consist of entering credentials and an IP address into the ExecuteSQL script step. I find the learning curve a little more steep.
      In this instance, it's really not hard for me to export the contents to my desktop once a day and then hoover them up into FMP. But can you recommend a one-stop tutorial for this kind of straightforward behavior?