Jump to content

  •  

UPGRADE DEADLINE - SEPTEMBER 26, 2014!
FileMaker Inc. has a deadline for users of version 10,11, 12 as Individual box or volume licenses (with expired maintenance).
If you don't renew your maintenance and upgrade to FMP 13 you will no longer be eligible to upgrade, at the discount pricing.

Volume Licensing upgrade pricing for FileMaker Pro 13, FileMaker Pro 13 Advanced and FileMaker Server 13 will be discontinued.
Individual upgrade pricing for FileMaker Pro 13 and FileMaker Pro 13 Advanced will increase after September 26, 2014.
As of 27-September-2014, FileMaker 10 products will no longer be available for purchase or support.

http://help.filemaker.com/app/answers/detail/a_id/13865


Photo

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

sql select

  • Please log in to reply
6 replies to this topic

#1 madmac33  newbie

madmac33
  • Newbies
  • PipPipPipPip
  • 4 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate

Posted 11 July 2014 - 03:11 AM

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.
 
 

  • 0

#2 Electric_Soul  novice

Electric_Soul
  • Members
  • 49 posts
  • FM Application:11 Advance
  • Platform:Windows XP
  • Skill Level:Intermediate
  • Time Online: 17h 37m 23s

Posted 11 July 2014 - 10:59 AM

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 :)


  • 0

#3 Fitch  Imaginary friend

Fitch
  • Moderators
  • 4,025 posts
  • LocationPortland, Oregon
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 12
  • Membership:TechNet
  • Time Online: 16d 9h 42m 43s

Posted 11 July 2014 - 04:57 PM

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

 

http://filemakerhack...esql-reference/


  • 0
Tom Fitch :: Portland, Oregon :: Fitch & Fitch: FileMaker consulting

#4 madmac33  newbie

madmac33
  • Newbies
  • PipPipPipPip
  • 4 posts
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate

Posted 12 July 2014 - 08:18 PM

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

 

http://filemakerhack...esql-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.


  • 0

#5 Electric_Soul  novice

Electric_Soul
  • Members
  • 49 posts
  • FM Application:11 Advance
  • Platform:Windows XP
  • Skill Level:Intermediate
  • Time Online: 17h 37m 23s

Posted 13 July 2014 - 08:53 PM

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
 


  • 0

#6 Brian C  FileMakerGeek

Brian C
  • Members
  • 447 posts
  • LocationPortland, OR
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:10, 11, 12, 13
  • Membership:TechNet, FileMaker Business Alliance
  • Time Online: 14h 15m 11s

Posted 15 July 2014 - 08:30 AM

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.
  • 0

#7 otmar  newbie

otmar
  • Newbies
  • PipPip
  • 2 posts
  • FM Application:12 Advance
  • Platform:Cross Platform
  • Skill Level:Expert
  • Membership:TechNet

Posted 21 July 2014 - 03:56 AM

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.

  • 0





FMForum Advertisers