
1. Re: doing maths with SQL and Filemaker?
user19752 Sep 27, 2014 6:22 PM (in response to camcorp)This is not documented but testing result shows that FM SQL is not support
refering column alias in column list (like as you do)
calculate using aggrigate function (like SUM(field1)/SUM(field2))
add:
workaround
1) use custom function
2) evaluating technique if your result is short enough (about 30,000 chars)
Let ( [
sql ="SELECT n,n+1,'<<<'  n  '/'  (n+1)  '>>>' FROM table WHERE n<12" ;
res = ExecuteSQL ( sql ; "" ; "" )
];
Evaluate ( Substitute ( Quote ( res ) ; [ "<<<" ; "\"&(" ] ; [ ">>>" ; ")&\""] ) )
)

2. Re: doing maths with SQL and Filemaker?
wimdecorte Sep 27, 2014 6:22 AM (in response to camcorp)camcorp wrote:
I don´t like using scripts to fill data, that's why I found until now SQL great.
Why don't you like scripts?
While SQL is a great tool, it is not always going to be faster than more traditional scripted approaches. In fact, sometimes it is going to be massively slower, especially if the complexity of the query goes up and the amount of math in the query increases (or any other operators like MAX(), ...)
So a little word of caution here, by discarding regular scripting and using calculated fields with SQL queries, your solution can become very suboptimal from a performance point of view. And from a maintenance point of view too. Scripts are easy to maintain, database schema not so much.

3. Re: doing maths with SQL and Filemaker?
camcorp Sep 28, 2014 9:11 PM (in response to user19752)Hi thanks!!
But I can't make it work the SQL syntax
regards
CAMCORP

4. Re: doing maths with SQL and Filemaker?
user19752 Sep 28, 2014 10:02 PM (in response to camcorp)On your case
sql="SELECT TimePeriod , SUM (Total_Sales) , TimePeriod , SUM (Total_Margin) , TimePeriod, '<<<'  SUM (Total_Margin)  '/'  SUM (Total_Sales)  '>>>'
FROM
...
It may be better using ROUND and *100 for percentage.
It seems your result will never contain < and > chars, so the <<< and >>> can be < and >.

5. Re: doing maths with SQL and Filemaker?
disabled_otmarkramis Sep 29, 2014 2:24 AM (in response to user19752)Theres a 3rd possability to do maths on aggregates:
3. VirtualList technic to do calculations on sqlaggregates.
Assuming the table VirtualList is filled with the global variable $$vl, the colomn names of the VL are Field01...FieldXX and the virtuallist column separator is "", you could do it like this.
Let ( [
$$vl = ExecuteSQL (
"SELECT TimePeriod , SUM (Total_Sales) AS totalsales, SUM (Total_Margin) AS totalmargin
FROM bbdd
GROUP by TimePeriod"
; "" ; ""; report::id_last_12M; report::branch )
] ;
ExecuteSQL (
"SELECT Field01, Field02, Field01, Field03, Field01, NUMVAL(Field03) / NUMVAL(Field02)
FROM VirtualList
WHERE Field01 IS NOT NULL"
; "" ; "" )
)
the NUMVAL operator is needed, if the virtuallists fields are text fields.
This all can be done in just one calculation step.
Regards Otmar

6. Re: doing maths with SQL and Filemaker?
camcorp Sep 29, 2014 7:00 AM (in response to user19752)Hi thanks again, but I can't make it work , the sql query works great up to '<<<' if I write more I get ? error sign.
regards
camcorp

7. Re: doing maths with SQL and Filemaker?
camcorp Sep 29, 2014 7:29 AM (in response to disabled_otmarkramis)Hi Otmar
thanks for your answer
I did as you suggested but the only result is the following
2013M0945795199
:
:
2014M081886522752
The second part of the calculation does not affect the result
best regards
camcorp

8. Re: doing maths with SQL and Filemaker?
camcorp Sep 29, 2014 7:31 AM (in response to wimdecorte)Hi wimdecorte
you are right with your suggestion, but I'm doing this only for report purposes, so speed its not an issue.
The main objective is to replace excel to provide a nice report that is easy to mantain and to deliver
best regards
camcorp

9. Re: doing maths with SQL and Filemaker?
disabled_otmarkramis Sep 29, 2014 7:36 AM (in response to camcorp)Hi Camcorp
You have to implement the VirtualList technic first. Search for "FileMaker VirtualList" or read this: http://www.mightydata.com/blog/virtuallistinthreeeasysteps/
The VirtualList is a table, where its records get populated by a $$ variable.
best regards
Otmar

10. Re: doing maths with SQL and Filemaker?
user19752 Sep 29, 2014 8:42 AM (in response to camcorp)Ouch! very sorry, I was a bonehead... using aggregation in calculation
This is working in my table, can you apply it ?
Let (
result = ExecuteSQL ( "
SELECT SUM(id), SUM(id+1), '<<<', SUM(id), '===', SUM(id+1), '>>>'
FROM testTable
GROUP BY text
" ; "" ; "" )
;
Evaluate ( Substitute ( Quote ( result ) ; [ "<<<," ; "\"&Round(" ] ; [ ",===," ; "/" ] ; [ ",>>>" ; "*100;2)&\"" ] ) )
)

11. Re: doing maths with SQL and Filemaker?
user19752 Sep 29, 2014 9:01 AM (in response to disabled_otmarkramis)If use a table to store the result, 'Import records from ODBC' and make a calculation field may be simple.

12. Re: doing maths with SQL and Filemaker?
camcorp Sep 29, 2014 9:51 AM (in response to user19752)I think I'm the bonehead guy
I did the following
Let (
result = ExecuteSQL ( "
SELECT SUM(Total_margin), SUM(Total_sales), '<<<', SUM(Total_margin), '===', SUM(Total_sales)), '>>>'
FROM bbdd
WHERE bbdd.TimePeriod >= ? AND bbdd.branch = ?
GROUP BY TimePeriod
" ; "" ; "" ;report::id_last_12M; report::branch)
;
Evaluate ( Substitute ( Quote ( result ) ; [ "<<<," ; "\"&Round(" ] ; [ ",===," ; "/" ] ; [ ",>>>" ; "*100;2)&\"" ] ) )
)
I undestand the formula but I can't get the error
I'm exploring a fourth aproach with a recursive calculation
regards
camcorp

13. Re: doing maths with SQL and Filemaker?
user19752 Sep 29, 2014 8:36 PM (in response to camcorp)You have too many closing ) here.
SELECT SUM(Total_margin), SUM(Total_sales), '<<<', SUM(Total_margin), '===', SUM(Total_sales)), '>>>'
If you have FMP advanced, watch the formula in data viewer as
Let ( r = ExecuteSQL ( ... ) ; If ( r <> "?" ; r ) )
Sometimes this help us, but sometimes only show a message "There is an error in the syntax of the query."

14. Re: doing maths with SQL and Filemaker?
disabled_otmarkramis Sep 30, 2014 1:33 AM (in response to user19752)The VirtiualList is not a table where a result get stored, it only reflects the result, abit like a temp table in memory. Once set up, it is a great construct for reporting.
have a look at the attached demo.

VLDemo.fmp12.zip 70.1 K
