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
"SELECT TimePeriod , SUM (Total_Sales) AS totalsales , TimePeriod , SUM (Total_Margin) AS totalmargin
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
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.