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 don´t like using scripts to fill data, that's why I found until now SQL great.

Kind regards.

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 ) ; [ "<<<" ; "\"&(" ] ; [ ">>>" ; ")&\""] ) )

)