~TheValue = ExecuteSQL ( "SELECT sum(thevalue) FROM records GROUP BY themonth" ; "" ; "" ) ;
GetAsNumber ( ~TheValue ) * 2 )
I concur with PSI. I dont think that Filemaker SQL can do the math portion of your request. Your Select Sum() will return an array that you should traverse and multiply.
Why SUM would return an Array, there's only one sum(value) per month.
Yes it seems filemaker can't do it unfortunately but this is terrible, It should work like MySQL, that considerably limit the scope of SELECT. It menas my query is impossible to do (of course it's much more complex than *2n, but due to taht it's impossible to do).
Argh, another afternoon wasted banging my head on an undocumented shortcomming that MySQL hasn't
Thanks for your help anyways.
P.S : I hoped to be able to convert the sum to something else, but didn't succeed with cast, numval etc
Have you tried the algebraically equivalent SUM ( theValue * 2 )?
I failed to clearly write what I was thinking. I meant to say that the ExecuteSQL ( whatever ) could return an array and did not make that clear. Sorry for my imprecision in communication.
Yes, that's why I wrote "I don't want to write sum(thevalue*2") :-)
Of course that example is just an example of a much much complicted query, involving JOIN, sub selects etc. In that query the *2 would be - cost.
I just needed to have confirmation that indeed this doesn't work, and taht there's no workarounds involving conversion possible.
Explaining the whole thing, would take too much time, and will probably end up in a too slow query. So I'm changing my method.
Here's the query, offending line in bold, but don't waste your time
You'll see that if do the operation in teh sum, it wil not at all give the same results. Basically, I want to remove all operational cost, from my order totals
SELECT A.annee_mois, B.partner_id,B.website_id,
COUNT(A."Order ID") as nbr_order,
SUM(C.ca_for_marge-c.orders_total_pmp +(C.shipping_cost-C.shipping_cost_real)) - (SELECT SUM(clics) FROM claimed_clics WHERE annee_mois_start=A.annee_mois AND partenaire_id=B.partner_id AND website_id=B.website_id) as ROI
FROM fluxs_tartanpion A
JOIN source_to_partners B
ON A.attribution_channel_lower=B.channel_lower AND A.attribution_source_lower=B.source_lower
JOIN orders_for_ROI C
ON A."Order ID"=C.orders_id
WHERE CAST(A."Date" AS date)>='01/09/2014' AND CAST(A."Date" AS date) <='30/09/2014' GROUP BY B.partner_id,B.website_id,A.annee_mois
since it "just *2" you could add it twice.
But where would you put the *2?
I think j is correct in that you can put this inside and it would be algebraically equivalent!
There's a workaround with useing a virtual list as sort of a temp table. Do the aggregates first and populate the virtual list with the result and do calculations on the aggregates with ExecuteSQL on the virtual list. This works instant and can be done in a single let-statement.
Actually a Feature Request
I see it does generate an error:
[FileMaker JDBC] FQL0005/(1:21): Expressions involving aggregations are not supported
it seems Expressions involving an Alias are also not supported
> Report it as an Issue ( bug )