doing maths with execute SQL in Filemaker

[camacorp]

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

2013M09 45795 2013M09 199 2013M10 53987 2013M10 769 2013M11 53217 2013M11 862 2013M12 52344 2013M12 691 2014M01 51521 2014M01 643 2014M02 50325 2014M02 625 2014M03 49698 2014M03 714 2014M04 181043 2014M04 3083 2014M05 195801 2014M05 2172 2014M06 193516 2014M06 1855 2014M07 191572 2014M07 2656 2014M08 188652 2014M08 2752

 

 

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 found SQL great but I can't do some calculations.

Kind regards.

camacorp

[timdietrich]

Hi there.

 

It looks like you are referencing the aliased summary values in the "percentmargin" calculation, and I don't think FQL supports that.

 

You might want to try this instead....

 

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

 

It is not efficient, but I do think it will work.

 

-- Tim