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

    • 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?
    • SQL Server 2008 ESS - Only displays system tables
      By Cerbera
      Hi all,
       
      This is my first post in the forum - one of no doubt many to come!
       
      I'm working on my first solution and as part of that I'm pulling in data from a SQL Server 2008 database which will be augmented by file maker tables.
       
      I have been able to locate the DSN and setup the External Data Source, however when I try to add the table in the relationships screen I can only see the system tables in the SQL database - not the views and tables that I have setup.
       
      Does anyone have an idea of a possible solution for this?
       
      Running FileMaker Pro Advanced 14 on Windows 7.
       
      Many thanks,
       
      Tim
    • ExecuteSQL - Field cannot be found
      By Pat Fellows
      Hello, 
      I am extremely new to working with SQL, so i am currently fumbling my way through achieving my required outcomes. 
      To explain further explain my issue, i created a table/layout on one of my databases that i could use to practice and visualize the outcomes of ExecuteSQL. 
      I type into my "query" field and that drives a separate ExecuteSQL calculation. 
      I have been able to achieve the required outcome in this field, however when i transfer the query directly into a ExecuteSQL calculation i get errors. These relate to the field in financials called "Cost Code"
      If i attempt the same calculation without it, i can run it without an issue. However what confuses me is it will work when input through a query field. 
      Query field;
      SELECT SUM(Total)
      FROM financials
      Where "Cost Code" = 'FA' and EID = '982 000147908607'
      Please find attached images highlighting and showing everything relevant. 
       
      Kind regards
      Pat
       


    • ExecuteSQL() sums and group by ERROR...
      By dwdata
      Hey gang,

      Trying to figure something for a fellow lister. I am tring to run a query in FileMaker using ExecuteSQL() that works perfectly when I run a similar query in a normal SQL editor:

      SELECT
      p.pk_ProductID,
      p.Description,
      SUM(i.Quantity) AS sQTY  

      FROM invoice_invoicelines_Product p  

      JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND i.fk_InvoiceID IN (1,2,3)  

      WHERE p.pk_ProductID IN (1,2,3)  

      GROUP BY p.pk_ProductID

      It produces a "?" (which is very informative).

      I guess need to know if this is too much for FileMaker's SQL engine or if FileMaker is going to force me to use something that is not tradition SQL protocol/syntax.
       
      Any feedback would be appreciated.
    • INSERT from Variable Array
      By sal88
      Hi
      I have a variable that contains multiple lines, each with fields separated by commas.
      Can I call up this variable in the SQL INSERT function in order to create new records (based on the variable)?

      Thanks