AnsweredAssumed Answered

doing maths with SQL and Filemaker?

Question asked by camcorp on Sep 26, 2014
Latest reply on Sep 30, 2014 by camcorp

Hi

I'm New usig SQL in FM

I succesfully made a query that retrieve grouped Data from a large database.

for the last 12 months I summarized information of two field.

I used the following SQL calculation

 

ExecuteSQL(

"SELECT TimePeriod , SUM (Total_Sales) AS totalsales , TimePeriod , SUM (Total_Margin) AS totalmargin

FROM bbdd

WHERE bbdd.id_TimePeriod >= ? AND bbdd.Branch = ?

GROUP by TimePeriod";

""; ¶; report::id_last_12M; report::branch)

 

the field display the data in this way for every branch

TimePeriod Total_Sales TimePeriod Margin

2013M09457952013M09199
2013M10539872013M10769
2013M11532172013M11862
2013M12523442013M12691
2014M01515212014M01643
2014M02503252014M02625
2014M03496982014M03714
2014M041810432014M043083
2014M051958012014M052172
2014M061935162014M061855
2014M071915722014M072656
2014M081886522014M082752

 

 

Right Now I can use the data for charting purposes, and work great and is very flexible.

I want to have more information and I'm trying to do the following calculation, the margin as a percentage of total Sales for each group of data base on the timeperiod line.

I triyed to do a new column like the following one

SELECT TimePeriod , SUM (Total_Sales) AS totalsales , TimePeriod , SUM (Total_Margin) AS totalmargin , TimePeriod, totalmargin / totalsales AS percentmargin

 

but it din't worked.

The question is what did I missed?

FM can't do the work I'm looking for?

I don´t like using scripts to fill data, that's why I found until now SQL great.

Kind regards.

camcorp

Outcomes