Jump to content

  •  

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,037 posts
  • LocationPortland, Oregon
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 12, 13
  • Membership:TechNet
  • Time Online: 16d 13h 20m 32s

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